查詢第二大數值

此處設置的數據庫（對於 sqlfiddle 來說太大了）： https ://pastebin.com/Hd0DbQ5z

這是一個 MS Access 數據庫。

我希望獲得最高分以及第二個最高分。我有以下查詢以獲得最高分，這是我正在尋找的確切結果：

SELECT e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN 
(SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

我想調整這個查詢以顯示第二名的分數，這是我得到的最接近的分數（但有一些意想不到的結果）：

SELECT e.GAME_VER, e.CLAN, e.SCORE, e.S_REGION
FROM dev AS e 
INNER JOIN (SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' AND ID NOT IN ( SELECT ID FROM dev as f INNER JOIN  (SELECT GAME_VER, MAX(SCORE) AS MAXVAL2 FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER) AS MAXSCORES2 ON  (f.SCORE = MAXSCORES2.MAXVAL2) AND (f.GAME_VER = MAXSCORES2.GAME_VER)) GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

（抱歉格式化，查詢有點亂）

此查詢為我提供了我正在尋找的結果，但它返回來自不同 S_REGION 值的行，即使我將它們限制在查詢中的單個區域。

+----------+---------+----------+----------+
| GAME_VER |  CLAN   |  SCORE   | S_REGION |
+----------+---------+----------+----------+
|     3.25 | Melons  | 93.97925 | USA      |
|    3.375 | Melons  | 94.96925 | USA      |
|      3.5 | Bananas |   95.883 | USA      |
|    3.625 | Grapes  |    96.74 | USA      |
|     3.75 | Bananas |   97.503 | USA      |
|    3.875 | Grapes  |   98.354 | USA      |
|     3.99 | Lemons  |   96.777 | USA      |
|        4 | Grapes  |   99.092 | USA      |
|    4.125 | Grapes  |   99.698 | USA      |
|     4.25 | Grapes  |   100.39 | USA      |
|    4.375 | Grapes  |  100.994 | USA      |
|    4.375 | Apples  |  100.994 | PANAMA   |
|      4.5 | Bananas |  101.618 | USA      |
|    4.625 | Bananas |   102.22 | USA      |
|     4.75 | Bananas |  102.804 | USA      |
|    4.875 | Bananas |  103.342 | USA      |
|     4.99 | Lemons  |  101.619 | USA      |
|        5 | Bananas |  103.823 | USA      |
|    5.125 | Bananas |  104.428 | USA      |
|     5.25 | Bananas |  105.014 | USA      |
|    5.375 | Bananas |   105.49 | USA      |
|      5.5 | Bananas |  105.944 | USA      |
|    5.625 | Grapes  |  106.379 | USA      |
|     5.75 | Grapes  |    106.5 | USA      |
|     5.75 | Grapes  |    106.5 | CANADA   |
+----------+---------+----------+----------+

出了什麼問題？或者有沒有更好的方法來編寫查詢以獲得我正在尋找的結果。（我是菜鳥）。

關於不匹配的區域：只有子查詢（即最裡面的聚合查詢）受到約束。這樣的約束不直接限制外部查詢。因此，如果來自另一個 S_REGION 的分數與來自受約束區域的分數匹配，那麼它也將在外部查詢中返回。那是除非您進一步約束外部查詢以具有相同的約束。

即使您可能沒有註意到，即使您的第一個查詢也是如此：如果有另一個區域得分並列，您也會從其他區域獲得值。試試看…添加與美國高分匹配的另一個區域的行，它將出現在您的第一個最高分查詢中。

也許更有用的是將 REGION 和 S_REGION 與 GAME_VER 完全相同，因為您在所有子查詢中按這些值分組，然後僅在最終查詢中添加特定約束。這不僅可以解決您返回不匹配區域的問題，還可以輕鬆地讓您通過僅刪除或更改外部查詢上的約束來查看任何區域的最高分數。這就是我將在下面的範例中執行的操作。

---

嵌入式子查詢便於在 Stack Overflow 上發布並將所有內容集中在一個地方。但是，Access 並不是一個完全優化的 SQL 引擎。它既不為每個子查詢創建也不儲存優化的執行計劃。對於 WHERE 子句中的子查詢，它幾乎總是會為每一行重新執行每個子查詢，即使理論上它可以只執行一次，然後再引用同一個臨時數據集。

Access 在優化已保存查詢方面做得更好。因此，我建議“保存子查詢”，然後在保存的查詢上使用 JOINS，而不是嵌入式 SQL。它允許更輕鬆地重用子查詢，並且在 Access 中它允許像普通查詢一樣編輯和查看子查詢。

---

考慮到所有這些，這是我建議的解決方案：

已保存的查詢 MAXSCORES：

SELECT e.REGION, e.S_REGION, e.GAME_VER, Max(e.SCORE) AS MAXVAL
FROM dev AS e
GROUP BY e.REGION, e.S_REGION, e.GAME_VER;

替換最高分的第一個查詢（在所需區域上正確過濾）：

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAXSCORES 
   ON (e.S_REGION = MAXSCORES.S_REGION) AND (e.REGION = MAXSCORES.REGION) 
     AND (e.GAME_VER = MAXSCORES.GAME_VER) AND (e.SCORE = MAXSCORES.MAXVAL)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN;

第二名的分數可以通過你的方式獲得（當然用適當的約束更新），但如前所述，我避免在 WHERE 子句中嵌入子查詢。此外，以下查詢不僅返回第二名分數，而且還指示哪些組沒有任何第二名分數——它返回空值。最後，該查詢在同一行的一個查詢中返回第一名和第二名的分數，這可以方便各種比較查詢。

已保存查詢 MAX2NDSCORE：

SELECT e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL, 
 Max(IIf([SCORE]<[mx].[MAXVAL],[SCORE],Null)) AS MAX2NDVAL
FROM dev AS e LEFT JOIN MAXSCORES AS mx 
   ON (e.REGION = mx.REGION) AND (e.S_REGION = mx.S_REGION) 
     AND (e.GAME_VER = mx.GAME_VER)
GROUP BY e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL;

返回所需區域的第二名氏族的查詢：

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAX2NDSCORES AS mx2
   ON (e.SCORE = mx2.MAX2NDVAL) AND (e.GAME_VER = mx2.GAME_VER)
     AND (e.S_REGION = mx2.S_REGION) AND (e.REGION = mx2.REGION)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN

最後，值得指出的是，這些查詢沒有正確考慮或辨識平分分數，但問題或表模式中沒有任何內容表明不能平分分數。查詢可以返回具有相同分數的多個氏族。

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