在 Oracle 中使用內部查詢進行多個分組

我有一個包含三列的表記錄：p_id, score, city。

P_id 得分城市

12 20 孟買
12 80 德里
12 50 浦那
13 20 孟買
13 30 德里
13 50 浦那
10 月 13 日 90 日

在這裡，我想查看p_id, max_score, max_score_city, min_score, min_score_city。這意味著對於每個玩家，我想要最高得分和得分所在的城市，最低得分和得分所在的城市，

p_id max_score max_score_city min_score min_score_city 
12 80 德里 20 孟買
10 月 30 日 13 月 90 日 孟買

我知道我需要在 p_id 上應用 group by 以獲得最低和最高分數，但是我如何加入 group by 以及如何將玩家和城市聯繫起來。

我是SQL新手，有人可以幫忙嗎？

WITH mins AS 
(SELECT P_id
   ,score AS min_score   
   ,city AS min_score_city    
   ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score) AS rn
)
, maxs AS 
(SELECT P_id
   ,score AS max_score   
   ,city AS max_score_city    
   ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score DESC) AS rn
)
SELECT maxs.P_id
   ,maxs.max_score
   ,maxs.max_score_city
   ,mins.min_score
   ,mins.min_score_city
FROM mins
JOIN maxs ON mins.P_id = maxs.P_id
WHERE mins.rn = 1 AND maxs.rn = 1

你可以用一個語句來做到這一點：

SELECT
 p_id
,MAX
   (
     CASE
       WHEN ScoreDesc = 1 THEN score
       ELSE NULL
     END
   ) AS max_score
,MAX
   (
     CASE
       WHEN ScoreDesc = 1 THEN city
       ELSE NULL
     END
   ) AS max_score_city
,MAX
   (
     CASE
       WHEN ScoreAsc = 1 THEN score
       ELSE NULL
     END
   ) AS min_score
,MAX
   (
     CASE
       WHEN ScoreAsc = 1 THEN city
       ELSE NULL
     END
   ) AS min_score_city
FROM
(
 SELECT
   p_id
  ,score
  ,city
  ,ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY Score, city) AS ScoreAsc
  ,ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY Score DESC, city) AS ScoreDesc
 FROM
   record
) RecordSummary
WHERE
 ScoreAsc = 1
   OR ScoreDesc = 1
GROUP BY 
 p_id

如果出現平局，應提及最小/最大城市將按字母順序選擇。

引用自：https://dba.stackexchange.com/questions/271226