取出一个成绩表中每科成绩最好的前两名(自联接和with as)

表结构如下：

CREATE TABLE [dbo].[stuScore](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [stuId] [int] NOT NULL,
 [stuName] [nvarchar](255) NOT NULL,
 [subject] [nvarchar](255) NOT NULL,
 [score] [int] NOT NULL
) ON [PRIMARY]

三种方法：

1.

select * from stuScore A where A.stuId in
    (select top 2 stuId from stuScore B where A.subject=B.subject order by B.score desc)
        order by A.score desc

2.

select * from stuScore A where 
  (select COUNT(*) from stuScore B where A.subject = B.subject and B.score>=A.score)<=2
  order by A.score desc

这两种方法查询结果不会按照科目分组：

3.利用with as

with cte as 
(
    select *,rn=row_number() over(partition by subject order by score desc )
    from stuScore 
)
select Id,stuId,stuName,subject,score from cte where rn<=2

结果如下：