例如:按相同的id,取各ID下Qty最大的两个数字,并按id和Qty升序排列。
SQL code id Qty
a 11
a 13
a 15
b 22
b 21
b 26
c 33
c 32
c 31
d 41
d 42
d 44
d 55
需要得到的结果
a 11
a 13
b 21
b 22
c 31
c 32
d 41
d 42请问该问题用SQL应如何实现?
SQL code id Qty
a 11
a 13
a 15
b 22
b 21
b 26
c 33
c 32
c 31
d 41
d 42
d 44
d 55
需要得到的结果
a 11
a 13
b 21
b 22
c 31
c 32
d 41
d 42请问该问题用SQL应如何实现?
(
select rownumber() over(partiton by id order by Qty desc) rn,
id,Qty from tab
)
select id,Qty
from t
where rn in (1,2)
order by id,Qty
(
select rownumber() over(partition by id order by Qty desc) rn,
id,Qty from tab
)
select id,Qty
from t
where rn in (1,2)
order by id,Qty
我在Microsoft SQL Server Management Studio Express中运行后出现了以下错误
顺便问下,如果我希望的不是最大两个而是最大的2%时,相应的SQL又应该如何?
麻烦大侠教教我~~~~
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'partiton'.
insert into @t
select 'a',11 union all
select 'a',13 union all
select 'a',15 union all
select 'b',22 union all
select 'b',21 union all
select 'b',26 union all
select 'c',33 union all
select 'c',32 union all
select 'c',31 union all
select 'd',41 union all
select 'd',42 union all
select 'd',44 union all
select 'd',55select * from @t a where Qty in (select top 2 Qty from @t where id=a.id)
/*
id Qty
---- -----------
a 11
a 13
b 22
b 21
c 33
c 32
d 41
d 42
*/
按照你的要求,结果如下:select id,Qty from
(select *,(select count(1) from tb where id=t.id and qty>=t.qty) ord from tb t) A
where ord<3
order by id,Qty
/*
id Qty
----- -----------
a 13
a 15
b 22
b 26
c 32
c 33
d 44
d 55(8 行受影响)*/
insert into @t
select 'a',11 union all
select 'a',13 union all
select 'a',15 union all
select 'b',22 union all
select 'b',21 union all
select 'b',26 union all
select 'c',33 union all
select 'c',32 union all
select 'c',31 union all
select 'd',41 union all
select 'd',42 union all
select 'd',44 union all
select 'd',55select * from @t a where Qty in (select top 2 Qty from @t where id=a.id order by Qty) order by 2
/*
id Qty
---- -----------
a 11
a 13
b 21
b 22
c 31
c 32
d 41
d 42
*/
在SQL Server Management Studio Express中调式2楼的SQL出现以下错误。
看起来像是rownumber不被识别的样子
Msg 195, Level 15, State 10, Line 1
'rownumber' is not a recognized built-in function name.
不过还是谢谢您了~~~~~