MSSQL这个SQL怎么写啊
这个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
[解决办法]
create table #tb(id int,sub int,type int)
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,2
select 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
/*
230
7111
582
*/
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)
*/