假設我有兩個表如下：
-------------- -------
| ID | Score |
-------------- -------
| 123 | 88 |
| 456 | 77 |
| 789 | 88 |
| 111 | 77 |
| 555 | 77 |
|444 | 88 |
| 222 | 77 |
| 333 | 88 |
第二張表：
-------------- -----
| ID |NAME | FScore |
-------------- -------
| 123 |John | 106 |
| 456 |Aaron | 99 |
| 789 |Dan | 105 |
| 111 |Kevin | 200 |
| 555 |Tom | 100 |
| 444 |Jeff | 120 |
| 222 |Carl | 65 |
| 333 |Wayne | 101 |
我想加入兩個表，并根據 FScore 找出前 3 行88和前 377行，如：
-------------- -------------- ------ -----
| ID | Score | NAME | FScore |
-------------- -------------- ------ -----
| 444 | 88 | Jeff | 120 |
| 123 | 88 | John | 106 |
| 789 | 88 | Dan | 105 |
任何幫助表示贊賞！
uj5u.com熱心網友回復：
您可以使用回傳連接表的 CTE，然后使用相關子查詢過濾結果集：
WITH cte AS (
SELECT *
FROM table1 t1 INNER JOIN table2 t2
ON t2.ID = t1.ID
WHERE t1.Score IN ('77', '88')
)
SELECT c1.ID, c1.Score, c1.Name, c1.FScore
FROM cte c1
WHERE (SELECT COUNT(*) FROM cte c2 WHERE c2.Score = c1.Score AND c2.FScore >= c1.FScore) <= 3;
或者，使用ROW_NUMBER()視窗函式：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY t1.Score ORDER BY t2.FScore DESC) rn
FROM table1 t1 INNER JOIN table2 t2
ON t2.ID = t1.ID
WHERE t1.Score IN ('77', '88')
)
SELECT ID, Score, Name, FScore
FROM cte
WHERE rn <= 3;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/442994.html
上一篇：將檔案名稱更改為父檔案夾名稱
下一篇：以資料形式存盤方法的結果