sql server分组，取每组的前几条数据

1、创建表

1 CREATE TABLE [dbo].[Student](
2     [name] [varchar](20) NULL,
3     [subject] [varchar](20) NULL,
4     [score] [int] NULL
5 )

2、插入数据

 1 INSERT INTO [dbo].[Student](name,subject,score) values('张三','语文',100)
 2 INSERT INTO [dbo].[Student](name,subject,score) values('李四','语文',60) 
 3 INSERT INTO [dbo].[Student](name,subject,score) values('王五','语文',99) 
 4 INSERT INTO [dbo].[Student](name,subject,score) values('赵六','语文',87) 
 5 
 6 INSERT INTO [dbo].[Student](name,subject,score) values('张三','数学',98) 
 7 INSERT INTO [dbo].[Student](name,subject,score) values('李四','数学',85) 
 8 INSERT INTO [dbo].[Student](name,subject,score) values('王五','数学',99) 
 9 INSERT INTO [dbo].[Student](name,subject,score) values('赵六','数学',60) 
10 
11 INSERT INTO [dbo].[Student](name,subject,score) values('张三','英语',66) 
12 INSERT INTO [dbo].[Student](name,subject,score) values('李四','英语',55) 
13 INSERT INTO [dbo].[Student](name,subject,score) values('王五','英语',99) 
14 INSERT INTO [dbo].[Student](name,subject,score) values('赵六','英语',88)

取每个科目的前三名：

方法一：

1 select [subject],[name],[score] from [dbo].[Student] T where [name] in (
2     select top 3 [name] from [dbo].[Student] where T.[subject] = [subject] 
3     group by [subject],[name],[score] order by [score] desc)
4 group by [subject],[name],[score] order by [subject] ,[score] desc

方法二：

1 select * from (select [subject],[name],[score],ROW_NUMBER() over(PARTITION by [subject] order by [score] desc) as num from [dbo].[Student]) T
2 where T.[num] <= 3 order by [subject]

 

posted on 2017-04-20 15:37  小灯泡  阅读(407)  评论(0)    收藏  举报

导航