现在表A里有字段id,name,loginTime
select * from A后结果如下
1 小明 2011-3-7
2 小明 2011-3-8
3 小明 2011-3-9
4 小黄 2011-3-8
5 小黄 2011-3-9但我想要的结果是
3 小明 2011-3-9
5 小黄 2011-3-9请问该语句该怎么写?
select * from A后结果如下
1 小明 2011-3-7
2 小明 2011-3-8
3 小明 2011-3-9
4 小黄 2011-3-8
5 小黄 2011-3-9但我想要的结果是
3 小明 2011-3-9
5 小黄 2011-3-9请问该语句该怎么写?
from a group by id,name;
[征集]分组取最大N条记录方法征集,及散分....
select * from A where loginTime='2011-3-9'
select * from tt a where not exists(select 1 from tt where a.name=name and a.id<id)
select a.* from tt a inner join
(select name,max(loginTime) as ma from tt group by name) b
on a.name=b.name and a.loginTime=b.ma
现在表A里有字段id,name,loginTime,act
select * from A后结果如下
1 小明 2011-3-7 A
2 小明 2011-3-8 C
3 小明 2011-3-9 A
4 小黄 2011-3-8 A
5 小黄 2011-3-9 C但我想要的结果是
3 小明 2011-3-9 A
5 小黄 2011-3-9 C请问该语句该怎么写?ACT代表一个动作这里用AC字母表示
2、6代码取loginTime最大
5代码取ID最大测试一下吧
create table dms.A
select 1 id, '小明' name, '2011-3-7' d, 'A' f union all
select 2 ,'小明','2011-3-8','C' union all
select 3 ,'小明','2011-3-9','A' union all
select 4,'小黄','2011-3-8','A' union all
select 5,'小黄','2011-3-9','C';select a2.*
from(
select a.name, max(a.d) md from dms.A a group by a.name
)a1, dms.A a2
where a1.name=a2.name
and a1.md=a2.d;-- 结果如下:
'3', '小明', '2011-3-9', 'A'
'5', '小黄', '2011-3-9', 'C'
drop table if exists dms.A;
create table dms.A
select 1 id, '小明' name, '2011-3-7' loginTime, 'A' act union all
select 2 ,'小明','2011-3-8','C' union all
select 3 ,'小明','2011-3-9','A' union all
select 4,'小黄','2011-3-8','A' union all
select 5,'小黄','2011-3-9','C';select a2.*
from(
select a.name, max(a.loginTime) md from dms.A a group by a.name
)a1, dms.A a2
where a1.name=a2.name
and a1.md=a2.loginTime;-- 结果如下:
'3', '小明', '2011-3-9', 'A'
'5', '小黄', '2011-3-9', 'C'
*************************** 1. row ***************************
Table: QA
Create Table: CREATE TABLE `qa` (
`id` bigint(20) NOT NULL DEFAULT '0',
`name` varchar(2) NOT NULL DEFAULT '',
`loginTime` varchar(8) NOT NULL DEFAULT '',
`act` varchar(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)mysql> SELECT * FROM QA;
+----+--------+-----------+-----+
| id | name | loginTime | act |
+----+--------+-----------+-----+
| 1 | 灏忔槑 | 2011-3-7 | A |
| 2 | 灏忔槑 | 2011-3-8 | C |
| 3 | 灏忔槑 | 2011-3-9 | A |
| 4 | 灏忛粍 | 2011-3-8 | A |
| 5 | 灏忛粍 | 2011-3-9 | C |
+----+--------+-----------+-----+
5 rows in set (0.00 sec)
mysql> SET NAMES GBK;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM qa a WHERE NOT EXISTS(SELECT 1 FROM qa WHERE a.NAME=NAME AN
D a.loginTime<loginTime);
+----+------+-----------+-----+
| id | name | loginTime | act |
+----+------+-----------+-----+
| 3 | 小明 | 2011-3-9 | A |
| 5 | 小黄 | 2011-3-9 | C |
+----+------+-----------+-----+
2 rows in set (0.02 sec)