求SQL，分类查询语句，写法！解决方法

求SQL，分类查询语句，写法！
表：
ID，类型，时间，其它

1 A 2012-04-23 18:00:00
2 A 2012-04-24 18:00:00 
3 A 2012-04-25 18:00:00
5 B 2012-04-23 18:00:00
6 B 2012-04-24 18:00:00 
7 C 2012-04-25 18:00:00
8 C 2012-04-23 18:00:00


我要按分类查询，得到 A,B,C中，时间最大的记录各1条。

我要的结果：

3 A 2012-04-25 18:00:00
6 B 2012-04-24 18:00:00
7 C 2012-04-25 18:00:00

这语法这么写？

[解决办法]
select * 
from (
select *, ROW_NUMBER()over(partition by leixing order by [time] desc) as row
from biao
) t
where t.row = 1
[解决办法]

探讨

引用:

select *
from (
select *, ROW_NUMBER()over(partition by leixing order by [time] desc) as row
from biao
) t
where t.row = 1


ROW_NUMBER()是啥东西？可直接这么写？

[解决办法]
declare @T table(ID int,类型 varchar(20),时间 datetime)
insert into @T
select 1,'A','2012-04-23 18:00:00' union all 
select 2,'A','2012-04-24 18:00:00' union all
select 3,'A','2012-04-25 18:00:00' union all
select 5,'B','2012-04-23 18:00:00' union all
select 6,'B','2012-04-24 18:00:00' union all
select 7,'C','2012-04-25 18:00:00' union all
select 8,'C','2012-04-23 18:00:00'



select *
from (
select *, ROW_NUMBER()over(partition by 类型 order by 时间 desc) as row
from @T
) t
where t.row = 1

[解决办法]
select b.Id,a.类型,a.时间
from 
(select 类型,max(时间) 时间 from xxx group by 类型) a left join
xxx b on a.类型 = b.类型 and a.时间 = b.时间

查看更多 下一篇