所以我必須從他們的每個組織中找到學生的最高分數，我通過應用 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
我在這里犯了什么錯誤？
uj5u.com熱心網友回復：
SELECT DISTINCT
organization,
FIRST_VALUE(name) OVER (PARTITION BY organization ORDER BY scores DESC) name,
MAX(scores) OVER (PARTITION BY organization) scores
FROM details
uj5u.com熱心網友回復：
您不能在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
uj5u.com熱心網友回復：
這是使用 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是一個保留詞，所以你必須小心使用它，總是將它嵌入到反引號中。
有關 CTE 的更多詳細資訊，請查看MySQL 公用表運算式。
在 MySQL 中，每個陳述句或查詢都會產生一個臨時結果或關系。公共表運算式或 CTE 用于命名存在于該特定陳述句的執行范圍內的那些臨時結果集
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484582.html
上一篇：SQL連接有一個我想要的兩倍列