如何获得在每个科目中获得最高分的学生的姓名?
我有下表
Name | Subject | Marks
--------------------------
a M 20
b M 25
c M 30
d C 44
e C 45
f C 46
g H 20
这里我有一个学生"表,我想获取获得的学生的姓名
学生表中每个科目的最高分数,如下面的输出.
Here I have a "Student" table I want to get the Name of the student who got
Max marks from each subject from the student table like the following OUTPUT.
Name | Subject | Marks
c M 30
f c 46
g h 20
推荐答案
您可以使用 ROW_NUMBER 函数仅返回每个主题的最佳"行:
You can use the ROW_NUMBER function to return only the "best" row per subject:
SQL 小提琴
MS SQL Server 2008 架构设置:
CREATE TABLE Student
([Name] varchar(1), [Subject] varchar(1), [Marks] int)
;
INSERT INTO Student
([Name], [Subject], [Marks])
VALUES
('a', 'M', 20),
('b', 'M', 25),
('c', 'M', 30),
('d', 'C', 44),
('e', 'C', 45),
('f', 'C', 46),
('g', 'H', 20)
;
查询 1:
SELECT Name, Subject, Marks
FROM(
SELECT *, ROW_NUMBER()OVER(PARTITION BY Subject ORDER BY Marks DESC) rn
FROM dbo.Student
)X
WHERE rn = 1
结果:
| NAME | SUBJECT | MARKS |
--------------------------
| f | C | 46 |
| g | H | 20 |
| c | M | 30 |
相关文章