一个简单的sql查询

Question

select id name gradename gradeid brownum age from user
得到的结果是
id name gradename gradeid brownum age
1 张三 一年级 1 11 7
8 张四 二年级 2 16 8
2 张五 二年级 2 17 7
4 张六 四年级 4 16 9
6 张七 三年级 3 18 7
5 张八 二年级 2 19 9
3 张九 四年级 4 21 10
7 张十 一年级 1 41 7
9 李一 一年级 1 71 7
10 李二 三年级 3 99 7
11 李四 四年级 4 88 10我想要的结果是 根据年级和点击率排序 只要每个学年的前两名brow最高 结果是
9 李一 一年级 1 71 7
7 张十 一年级 1 41 7
5 张八 二年级 1 19 9
2 张五 二年级 2 17 7
10 李二 三年级 3 99 7
6 张七 三年级 3 18 7
11 李四 四年级 4 88 10
3 张九 四年级 4 21 10
请大神指点

Answer 1

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go 
create table [test]([id] int,[name] varchar(4),[gradename] varchar(6),[gradeid] int,[brownum] int,[age] int)
insert [test]
select 1,'张三','一年级',1,11,7 union all
select 8,'张四','二年级',2,16,8 union all
select 2,'张五','二年级',2,17,7 union all
select 4,'张六','四年级',4,16,9 union all
select 6,'张七','三年级',3,18,7 union all
select 5,'张八','二年级',2,19,9 union all
select 3,'张九','四年级',4,21,10 union all
select 7,'张十','一年级',1,41,7 union all
select 9,'李一','一年级',1,71,7 union all
select 10,'李二','三年级',3,99,7 union all
select 11,'李四','四年级',4,88,10
goselect * from test a
where [brownum]=(select top 2 [brownum] from test b where a.[name]=b.[name] order by [brownum] desc)/*
1 张三 一年级 1 11 7
8 张四 二年级 2 16 8
2 张五 二年级 2 17 7
4 张六 四年级 4 16 9
6 张七 三年级 3 18 7
5 张八 二年级 2 19 9
3 张九 四年级 4 21 10
7 张十 一年级 1 41 7
9 李一 一年级 1 71 7
10 李二 三年级 3 99 7
11 李四 四年级 4 88 10
*/

Answer 2

select 
  distinct b.*
from
  user a
cross apply
  (select top 2 * from user where gradeid=t.gradeid order by brownum desc)b

Answer 3

select id,name,gradename,gradeid,brownum,age 
from
(
select row_number() over(partition by gradeid order by brownum desc) rn,* 
from
(
select id name gradename gradeid brownum age from user
)t
)tt
where tt.rn<=2

Answer 4

楼主的是SQl2005以上版本用row_number写法或者cross apply写法--row_number
select tt.* 
from
(
    select rn=row_number() over(partition by gradeid order by brownum desc),* 
    from
    (
        select id name gradename gradeid brownum age from user
    )t
)tt
where tt.rn<=2--cross apply
select 
  distinct b.*
from
  user a
cross apply
  (select top 2 * from user where gradeid=t.gradeid order by brownum desc)b
sql2000版本的用下面写法
-->注意要先group by 年纪分组后取点击率排名前2位的
select * from test a
where [brownum]=(select top 2 [brownum] from test b where a.[name]=b.[name] group by gradeid order by [brownum] desc)

Answer 5

select  * from test a
where [brownum]IN (select top 2 [brownum] from test b where a.[gradename]=b.[gradename] order by [brownum] desc)
ORDER BY [gradename]  ,[brownum] DESC

Answer 6

DECLARE @userinfo TABLE (id INT IDENTITY, NAME NVARCHAR(20), gradename NVARCHAR(10), gradeid INT , brownum INT , age INT ) 
INSERT INTO @userinfo (  NAME , gradename , gradeid , brownum , age )
SELECT '张三' ,'一年级',1,11,7 UNION ALL
SELECT '张五' ,'二年级',2,17,7 UNION ALL
SELECT '张九', '四年级', 4, 21, 10 UNION ALL
SELECT '张六', '四年级' ,4 ,16, 9 UNION ALL
SELECT '张八' ,'二年级' ,2 ,19, 9 UNION ALL
SELECT '张七', '三年级', 3 ,18, 7 UNION ALL
SELECT '张十' ,'一年级' ,1 ,41 ,7 UNION ALL
SELECT '张四', '二年级', 2, 16, 8 UNION ALL
SELECT '李一' ,'一年级' ,1 ,71 ,7 UNION ALL
SELECT '李二', '三年级' ,3 ,99 ,7 UNION ALL
SELECT '李四' ,'四年级' ,4 ,88 ,10
SELECT * FROM 
(
SELECT  a.id,a.NAME,a.gradename,a.gradeid,a.brownum,a.age FROM @userinfo a 
WHERE  exists 
(
select * FROM (SELECT MAX(brownum) AS brownum FROM @userinfo GROUP BY gradeid) b 
WHERE a.brownum=b.brownum
)
UNION 
SELECT * FROM @userinfo a 
WHERE EXISTS (
SELECT MAX(u.brownum) FROM @userinfo u
WHERE NOT exists 
(
SELECT  * FROM @userinfo a 
WHERE a.id=u.id AND  exists 
(
select * FROM (SELECT MAX(brownum) AS brownum FROM @userinfo GROUP BY gradeid) b 
WHERE a.brownum=b.brownum
)
)
GROUP BY u.gradeid
HAVING a.brownum=MAX(u.brownum)
 )
) x ORDER BY gradeid ASC , brownum DESC