表:
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这语法这么写?
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这语法这么写?
有几个类型,就得到几条记录。 你 GROUO BY ID ,显示 只有1条吧。
from (
select *, ROW_NUMBER()over(partition by leixing order by [time] desc) as row
from biao
) t
where t.row = 1
ROW_NUMBER()是啥东西?可直接这么写?
FROM xxx group by 类型 )s
inner join xxx b
on s.时间= b.时间
select * from 表 as a where not exists(select * from 表 as b where a.类型=b.类型 and a.时间<b.时间);
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
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,B.类型,B.时间 from
(select 类型,max(时间) as '时间' from @T group by 类型) as A,@T as B
where A.时间=B.时间 and A.类型=B.类型
from
(select 类型,max(时间) 时间 from xxx group by 类型) a left join
xxx b on a.类型 = b.类型 and a.时间 = b.时间