内连接两个表并列出特定行

Question

假设我有两个表，如下所示：

+--------------+-------+
| 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 和前 3 个 77 行，例如：

+--------------+--------------+------+-----+
| ID        | Score   | NAME    | FScore | 
+--------------+--------------+------+-----+
| 444       | 88      |  Jeff  | 120 | 
| 123       | 88      |  John  |  106   | 
| 789       | 88      |  Dan  |  105   |      

任何帮助很感激！

Answer 1

您可以使用返回连接表的 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;

请参阅 演示。