如何在sql/sqlalchemy中使用join和orderbywith多个记录？

i34xakig 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(379)

我有两张table，看起来像这样：
学生信息：

| student_id | major |
|------------|-------|
| 1001       | CS    |
| 1002       | CS    |
| 1003       | CS    |
| 1004       | CS    |
| 1005       | BI    |

学生成绩：

| 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   |

现在我想选专业前两名 CS 等级 CS.201 在 2016.Spring ，因此结果可能如下所示：

| 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    |

注意，因为有两个学生 99 在 CS.201 ，因此我们需要所有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

sql mysql Database sqlalchemy

来源：https://stackoverflow.com/questions/50012997/how-to-use-join-and-order-by-with-multiple-records-in-sql-sqlalchemy

2条答案

按热度按时间

zfycwa2u1#

这个问题需要用到 DENSE_RANK 不幸的是，功能 DENSE_RANK 函数只支持高于mysql 8.0的版本。
你需要做出决定 rank 在 student_grade ，这样就可以编写子查询来创建排名结果集，然后 join 在 student_info ```
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`

赞(0）回复(0）举报 2021-06-24

mrphzbgm2#

你可以试试mysql window function 与 DENSE_RANK() . 您可以在这里查阅文档。

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;

希望这对你有帮助，祝你好运！

赞(0）回复(0）举报 2021-06-24

我来回答

如何在sql/sqlalchemy中使用join和orderbywith多个记录？

2条答案

相关问题

热门标签

最新问答