获取分组数据中的最新数据

 

数据表 test

id      type    name          time

1        1        aa         1987-09-22 01:00:00

2        1        bb         1987-09-22 02:00:00

3        1        cc         1987-09-22 03:00:00

4        2        dd         1987-09-22 04:00:00

5        2        ee         1987-09-22 05:00:00

6        2         ff         1987-09-22 06:00:00

 

 

查询后效果

 

id      type    name          time

3        1        cc         1987-09-22 03:00:00

6        2         ff         1987-09-22 06:00:00

 

 

 

sql语句

 

select a.* from test as a,(select type,max(time) as time from test group by type) as b where a.type=b.type and a.time=b.time

 

select a.* from test as a inner join (select type,max(time) as time from test group by type) as b on  a.type=b.type and a.time=b.time