>我有 2 个表看起来像这样:
student_info:
| student_id | major |
|------------|-------|
| 1001 | CS |
| 1002 | CS |
| 1003 | CS |
| 1004 | CS |
| 1005 | BI |
student_grade:
| student_id | course | semester | grade |
|------------|--------|-------------|-------|
| 1001 | CS.201 | 2016.Spring | 100 |
| 1001 | CS.202 | 2016.Fall | 90 |
| 1001 | EE.201 | 2016.Spring | 90 |
| 1002 | CS.201 | 2016.Spring | 70 |
| 1002 | CS.202 | 2016.Fall | 70 |
| 1003 | CS.201 | 2016.Spring | 99 |
| 1003 | EE.201 | 2016.Fall | 90 |
| 1003 | CS.202 | 2016.Fall | 90 |
| 1004 | CS.201 | 2016.Spring | 99 |
| 1004 | BI.202 | 2016.Fall | 80 |
| 1005 | CS.201 | 2017.Spring | 100 |
现在我想选择2016.Spring
CS
年级CS.201
专业的前 2 名学生,所以结果可能如下所示:
| student_id | major | semester | course | grade |
|------------|-------|-------------|--------|-------|
| 1001 | CS | 2016.Spring | CS.201 | 100 |
| 1003 | CS | 2016.Spring | CS.201 | 99 |
| 1004 | CS | 2016.Spring | CS.201 | 99 |
请注意,由于有 2 名学生在CS.201
中获得了99
,因此我们想要所有 3 条记录(而不仅仅是使用limit(2)
)。
数据库是MySQL。
我的 sql 脚本看起来像:
SELECT student_info.student_id,
student_info.major,
student_grade.semester,
student_grade.course,
student_grade.grade
FROM student_info, student_grade
WHERE student_info.major = 'CS'
AND student_info.student_id = student_grade.student_id
AND student_grade.semester = '2016.Spring'
AND student_grade.course = 'CS.201'
ORDER BY student_grade.grade DESC
LIMIT 2
问题需要使用DENSE_RANK
函数,不幸的是DENSE_RANK
函数仅支持MySQL 8.0以上的版本。
您需要在student_grade
上进行rank
,因此您可以编写子查询来创建排名结果集,然后在student_info
上join
SELECT b.student_id,
b.major,
a.semester,
a.course,
a.grade
FROM (SELECT student_id,
grade,
semester,
course,
@prev := @curr,
@curr := grade,
@rank := IF(@prev = @curr, @rank, @rank + 1) AS rank
FROM student_grade,
(SELECT @curr := NULL,
@prev := NULL,
@rank := 0) s
WHERE course = 'CS.201'
AND semester = '2016.Spring'
ORDER BY grade DESC) a
INNER JOIN student_info b
ON a.student_id = b.student_id
WHERE a.rank <= 2
AND b.major = 'CS'
sqlfiddle:https://www.db-fiddle.com/f/tkU4UfRE3AZziiEn4HiLwF/0
解释:
@prev := @curr
让@curr
是以前的年级@curr := grade
变量中设置当前等级@curr@rank := IF(@prev = @curr, @rank, @rank+1) AS rank
检查@prev
变量和变量@curr
相同。如果它们相同,则使用相同的rank
否则rank + 1
你可以用DENSE_RANK()
试试 mysqlwindow function
。您可以在此处参考文档。
SELECT * FROM
(
SELECT
a.*,
DENSE_RANK() OVER w AS 'rank'
FROM
(
SELECT
g.student_id,
i.major,
g.course,
g.semester,
g.grade
FROM
student_grade g
JOIN student_info i
ON i.student_id = g.student_id
WHERE i.major = 'CS'
AND g.semester = '2016.Spring'
AND g.course = 'CS.201'
) a
WINDOW w AS (ORDER BY a.grade DESC)
) b
WHERE b.rank <= 2;
希望这个帮助,祝你好运!