我目前正在使用一个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。
任何人都可以帮助我确定我的问题在我的查询或建议一种替代方法来实现所需的结果?任何见解或更正查询结构将不胜感激.
1条答案
按热度按时间nfzehxib1#
这就是我所做的
字符串