mysql SQL查询问题-获取特定学生的级别和职位

vwhgwdsa 于 2024-01-05 发布在 Mysql

关注(0)|答案(1)|浏览(163)

我目前正在使用一个SQL查询，根据特定学生在特定班级、部分和课程中的平均分数来检索他们的排名和位置。然而，我遇到了一个问题，当我包含一个WHERE子句来获取特定学生的详细信息时，查询只返回最高排名（1）。

以下是我的查询

WITH RankedAverages AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range
    FROM
        ftscores_primary
    WHERE
        `student_id`=870 AND 
        class_id = 9 AND
        section_id = 3 AND
        session_id = 19
    GROUP BY
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`
),
            RankedWithDenseRank AS (
                SELECT
                 `student_id`,
                      `class_id`,
                     `section_id`,
                     `session_id`,
                    unique_average,
                    DENSE_RANK() OVER (ORDER BY average_range DESC) AS dense_rank
                FROM
                    RankedAverages
            ),
            RankedWithPositions AS (
                SELECT
                 `student_id`,
                      `class_id`,
                     `section_id`,
                     `session_id`,
                    unique_average,
                    CASE
                        WHEN RANK() OVER (ORDER BY unique_average DESC) = 1 THEN 1
                        WHEN RANK() OVER (ORDER BY unique_average DESC) = 2 THEN 2
                        WHEN RANK() OVER (ORDER BY unique_average DESC) = 3 THEN 3
                        ELSE dense_rank + 1
                    END AS position
                FROM
                    RankedWithDenseRank
            )
            SELECT
             `student_id`,
                      `class_id`,
                     `section_id`,
                     `session_id`,
                unique_average,
                position
            FROM
                RankedWithPositions
            ORDER BY
                position, unique_average DESC;

字符串
如果没有这个WHERE子句student_id = 870，我将得到一个关于某个班级学生等级的漂亮表格。
但是，如果我添加WHERE子句student_id = 870，它在位置列中输出1，但真实的位置是4。
任何人都可以帮助我确定我的问题在我的查询或建议一种替代方法来实现所需的结果？任何见解或更正查询结构将不胜感激.

mysql

来源：https://stackoverflow.com/questions/77667740/sql-query-issue-fetching-rank-and-position-for-a-specific-student

1条答案

按热度按时间

nfzehxib1#

这就是我所做的

WITH RankedAverages AS (
    SELECT
        student_id,
        class_id,
        section_id,
        session_id,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range
    FROM ftscores_primary
    WHERE session_id = 19 AND 
    class_id =9 AND 
    section_id = 3
    GROUP BY student_id
),
RankedWithDenseRank AS (
    SELECT
        student_id,
        class_id,
        section_id,
        session_id,
        unique_average,
        DENSE_RANK() OVER (ORDER BY average_range DESC) AS dense_rank
    FROM RankedAverages
),
RankedWithPositions AS (
    SELECT
        student_id,
        class_id,
        section_id,
        session_id,
        unique_average,
        CASE
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 1 THEN 1
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 2 THEN 2
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 3 THEN 3
            ELSE dense_rank + 1
        END AS position
    FROM RankedWithDenseRank
)
SELECT
    student_id,
    class_id,
    section_id,
    session_id,
    unique_average,
    position
FROM RankedWithPositions
WHERE student_id = 870 
ORDER BY position, unique_average DESC;

字符串

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

mysql SQL查询问题-获取特定学生的级别和职位

1条答案

相关问题

热门标签

最新问答