22,209
社区成员
发帖
与我相关
我的任务
分享根据多字段排名
现在有一个成绩表，想取每科subject 成绩前两名的名单(标红色记录)，如何写sql呢，工具是mysql
id name subject score rank
5 代金洪 build 89 1
6 邓先见 build 86 2
53 杨波 build 86 2
65 赵锡超 build 86 2
68 钟源 build 84 3
74 李佼健 chinese 91 1
6 邓先见 chinese 90 2
5 代金洪 chinese 88 3
13 胡小伟 chinese 87 4
68 钟源 chinese 85 5
4 陈刚 chinese 84 6
74 李佼健 draft 98 1
6 邓先见 draft 93 2
58 曾伟 draft 93 3
14 黄鑫 draft 91 4
66 赵锡军 draft 91 5
68 钟源 draft 89 6
5 代金洪 draft 87 7
id name subject score rank
5 代金洪 build 89 1
6 邓先见 build 86 2
53 杨波 build 86 2
65 赵锡超 build 86 2
68 钟源 build 84 3
74 李佼健 chinese 91 1
6 邓先见 chinese 90 2
5 代金洪 chinese 88 3
13 胡小伟 chinese 87 4
68 钟源 chinese 85 5
4 陈刚 chinese 84 6
74 李佼健 draft 98 1
6 邓先见 draft 93 2
58 曾伟 draft 93 3
14 黄鑫 draft 91 4
66 赵锡军 draft 91 5
68 钟源 draft 89 6
5 代金洪 draft 87 7
...全文
请发表友善的回复…
发表回复
RICHEER COCA 2017-06-27
- 打赏
- 举报
----测试数据结束
--select * from #T a where score in (select top 2 score FROM #T WHERE a.subject = subject order by score desc)
RINK_1 2017-06-27
- 打赏
- 举报
试试这个
SELECT * FROM
(SELECT *,(SELECT COUNT(DISTINCT SCORE) FROM #T WHERE SUBJECT=A.SUBJECT AND SCORE>=A.SCORE) AS RANK FROM TABLE A) AS A
WHERE RANK<=2
二月十六 2017-06-26
- 打赏
- 举报
用mssql写的，mysql语法一样
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(23),[subject] nvarchar(27),[score] int)
Insert #T
select 5,N'代金洪',N'build',89 union all
select 6,N'邓先见',N'build',86 union all
select 53,N'杨波',N'build',86 union all
select 65,N'赵锡超',N'build',86 union all
select 68,N'钟源',N'build',84 union all
select 74,N'李佼健',N'chinese',91 union all
select 6,N'邓先见',N'chinese',90 union all
select 5,N'代金洪',N'chinese',88 union all
select 13,N'胡小伟',N'chinese',87 union all
select 68,N'钟源',N'chinese',85 union all
select 4,N'陈刚',N'chinese',84 union all
select 74,N'李佼健',N'draft',98 union all
select 6,N'邓先见',N'draft',93 union all
select 58,N'曾伟',N'draft',93 union all
select 14,N'黄鑫',N'draft',91 union all
select 66,N'赵锡军',N'draft',91 union all
select 68,N'钟源',N'draft',89 union all
select 5,N'代金洪',N'draft',87
Go
--测试数据结束
SELECT *
FROM #T a
WHERE score IN ( SELECT TOP 2
score
FROM #T
WHERE a.subject = subject
ORDER BY score DESC );
skysenlin 2017-06-26
- 打赏
- 举报
对不起，我没表述清楚，没有rank这个字段，我是为了解释清楚才写的这个字段
二月十六 2017-06-26
- 打赏
- 举报
SELECT * FROM 表 WHERE `RANK` IN(1,2)RICHEER COCA 2017-06-26
- 打赏
- 举报
想取每科subject 成绩前两名的名单，是要 #2 这样的吗？
RICHEER COCA 2017-06-26
- 打赏
- 举报
结果是
id name subject score rank
5 代金洪 build 89 1
6 邓先见 build 86 2
53 杨波 build 86 2
65 赵锡超 build 86 2
74 李佼健 chinese 91 1
6 邓先见 chinese 90 2
74 李佼健 draft 98 1
6 邓先见 draft 93 2
RICHEER COCA 2017-06-26
- 打赏
- 举报
这是楼主需要的结果吗
USE
tempdb --本机环境为 Microsoft SQL Server 2014
--建立测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(10),[subject] varchar(10),[score] char(2),[rank] int)
Insert #T
select N'5','代金洪','build ','89','1' union all
select N'6','邓先见','build ','86','2' union all
select N'53','杨波','build ','86','2' union all
select N'65','赵锡超','build ','86','2' union all
select N'68','钟源','build ','84','3' union all
select N'74','李佼健','chinese','91','1' union all
select N'6','邓先见','chinese','90','2' union all
select N'5','代金洪','chinese','88','3' union all
select N'13','胡小伟','chinese','87','4' union all
select N'68','钟源','chinese','85','5' union all
select N'4','陈刚','chinese','84','6' union all
select N'74','李佼健','draft','98','1' union all
select N'6','邓先见','draft','93','2' union all
select N'58','曾伟','draft','93','3' union all
select N'14','黄鑫','draft','91','4' union all
select N'66','赵锡军','draft','91','5' union all
select N'68','钟源','draft','89','6' union all
select N'5','代金洪','draft','87','7'
Go
--测试查询数据 检查数据 -- select* from #T
select * from #T
where rank between 1 and 2 order by subject
