带WHERE子句的Rank()函数不显示MYSQL

Question

因此，我必须从他们的每个组织中找出学生的最高分数，然后通过应用秩()函数来解决这个问题：

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

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

要解决这个问题，请将查询更改为使用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 2

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

注意：rank是一个保留词，所以您必须小心使用它，始终将它嵌入后台。

有关CTE的更多详细信息，请查看MySQL通用表表达式。

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