这个SQL怎么写啊
id sub type
2 3 0
5 8 2
3 7 1
7 11 1
9 2 2
如何求得下面的表,规则是type各选出一条(应该是GROUPBY吧),并且以SUB倒序
id sub type
2 3 0
7 11 1
5 8 2
id sub type
2 3 0
5 8 2
3 7 1
7 11 1
9 2 2
如何求得下面的表,规则是type各选出一条(应该是GROUPBY吧),并且以SUB倒序
id sub type
2 3 0
7 11 1
5 8 2
from tb a
inner join (select type,max(sub) as sub from tb group by type) b
on a.type=b.type and a.sub=b.sub
insert into #tb
select 2,3,0
union all select 5,8,2
union all select 3,7,1
union all select 7,11,1
union all select 9,2,2select a.*
from #tb a
inner join (select type,max(sub) as sub from #tb group by type) b
on a.type=b.type and a.sub=b.sub
order by a.type/*
2 3 0
7 11 1
5 8 2
*/
create table fu
(id int, sub int, type int)insert into fu
select 2, 3, 0 union all
select 5, 8, 2 union all
select 3, 7, 1 union all
select 7, 11, 1 union all
select 9, 2, 2
-- 方法1
select * from fu a
where not exists
(select 1 from fu b
where b.type=a.type and b.sub>a.sub)
/*
id sub type
----------- ----------- -----------
2 3 0
5 8 2
7 11 1(3 row(s) affected)
*/
-- 方法2
select t.id,t.sub,t.type
from (select *,
row_number() over(partition by [type] order by sub desc) 'rn'
from fu) t
where t.rn=1/*
id sub type
----------- ----------- -----------
2 3 0
7 11 1
5 8 2(3 row(s) affected)
*/
row_number() over(partition by [type] order by sub desc) 'rn'
这个OVER是什么作用和意思啊。
参考 http://msdn.microsoft.com/zh-cn/library/ms186734(v=sql.105).aspx