SQL语句查询 求大神 坐等
0
悬赏园豆：5
[待解决问题]
CREATE TABLE hard(
id int IDENTITY(1,1) primary key ,
aa varchar(50) ,
bb int ,
)
insert into hard values('a',9)
insert into hard values('a',7)
insert into hard values('a',8)
insert into hard values('a',6)
insert into hard values('b',2)
insert into hard values('b',3)
insert into hard values('b',4)
insert into hard values('c',1)
insert into hard values('c',8)
insert into hard values('c',2)
要求查询出每个品种值最大的前2项
查询结果如下
a 9
a 8
b 4
b 3
c 8
c 2
所有回答(2)
0
SELECT [id] ,[aa] ,[bb] FROM hard where id in (select top 2 id from hard as innerTable where innerTable.aa=hard.aa order by bb desc)
执行了你写的sql，但是发现aa为b的值的顺序是反的，不是按照bb降序排列，请问这是怎么回事啊
哥 有问题啊
0
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY aa ORDER BY bb DESC ) AS r
FROM hard
) AS t
WHERE t.r <= 2 亲测有效
学习了
