带有WHERE子句的Rank()函数不显示任何MYSQL内容

Question

所以我必须从每个组织中找到学生的最大分数，我通过应用RANK()函数提出了解决方案:

SET SQL_SAFE_UPDATES = 0;
INSERT INTO interviewqs.details(scores,name,organization)
VALUES
(30,"Daniel","OWARD UNI"),
(40,"Kayla","OWARD UNI"),
(12,"Hope","ZELENSKY UNI"),
(50,"Osman","ZELENSKY UNI"),
(4,"Daniel","REWARD UNI"),
(77,"Joe","REWARD UNI");
DESCRIBE interviewqs.details;
# Find the student with highest scores from each organization

SELECT DISTINCT organization,name,scores,
RANK() OVER (PARTITION BY organization ORDER BY scores DESC)
AS "rank"
FROM details
WHERE "rank" = 1;

问题是当我执行代码时，输出显示空表，

没有使用'WHERE'函数

organization name scores rank 
OWARD UNI   Kayla   40  1
OWARD UNI   Daniel  30  3
REWARD UNI  Daniel  77  1
REWARD UNI  Daniel  30  2
REWARD UNI  Daniel  4   4
ZELENSKY UNI Osman  50  1
ZELENSKY UNI Hope   12  3

使用'WHERE'函数

organization name scores rank 

我在这里犯了什么错误?

Answer 1

SELECT DISTINCT
organization,
FIRST_VALUE(name) OVER (PARTITION BY organization ORDER BY scores DESC) name,
MAX(scores) OVER (PARTITION BY organization) scores
FROM details

Answer 2

不能在WHERE子句中使用窗口函数。原因是WHERE子句在窗口函数之前被先处理。我强烈建议你读一篇文章，为什么我不能在Where子句中使用RANK()

要解决这个问题，请将查询更改为使用CTE或子查询，如下所示:

子查询:

SELECT organization, name, scores
FROM (
SELECT 
organization, name, scores,
RANK() OVER(PARTITION BY organization ORDER BY scores DESC) AS rnk
FROM details
) tmp
WHERE rnk = 1

Answer 3

这是使用CTE的另一种方式。

WITH tmp AS 
(
SELECT DISTINCT organization, `name`, scores,
RANK() OVER (PARTITION BY organization ORDER BY scores DESC) `rank`
FROM details
)
SELECT organization, `name`, scores FROM tmp WHERE `rank` = 1;

DB小提琴

注意:rank是一个保留词，所以你必须小心使用它，总是把它嵌入在反号中。

查看MySQL公共表表达式了解更多关于CTE的详细信息。

在MySQL中，每个语句或查询都会产生一个临时结果或关系。公共表表达式或CTE用于命名存在于特定语句

执行范围内的临时结果集。