如何合并两个SQL查询结果？Skat俱乐部赛季积分计算

需求说明 #

我们的Skat纸牌俱乐部每年每周开展一次活动，每位成员最多有52次成绩记录。赛季冠军判定规则为：将个人21次最佳成绩与5次最差成绩的积分求和。目前已有两个独立SQL分别计算这两部分积分总和，需要将它们合并为一个包含赛季、成员姓名、最佳21次积分、最差5次积分及总积分的结果集。

合并方案 #

可以通过**公共表表达式（CTE）**分别为两个子查询设置别名（top21_scores 和 bottom5_scores），再通过Season和Name字段将两个结果集关联，最终计算总积分。

完整SQL代码 #

WITH top21_scores AS (
    SELECT 
        rs.Season, 
        rs.Name, 
        SUM(rs.Pkt) AS Pkt_Max21 
    FROM (
        SELECT 
            Season, 
            Name, 
            Pkt, 
            ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Pkt DESC) AS Row,
            RANK() OVER (PARTITION BY Name ORDER BY Pkt DESC) AS Rank
        FROM Gameresults
        WHERE Season = (SELECT Year FROM Evaluation) 
          AND Member = 1
    ) rs 
    WHERE rs.Rank <= 21 
      AND rs.Row <= 21 
    GROUP BY rs.Season, rs.Name 
),
bottom5_scores AS (
    SELECT 
        rs.Season, 
        rs.Name, 
        SUM(rs.Pkt) AS Pkt_Min5
    FROM (
        SELECT 
            Season, 
            Name, 
            Pkt,
            RANK() OVER (PARTITION BY Name ORDER BY Pkt ASC) AS Rank
        FROM Gameresults
        WHERE Season = (SELECT Year FROM Evaluation) 
          AND Member = 1
    ) rs 
    WHERE rs.Rank <= 5 
      AND rs.Name IN (
          SELECT Name 
          FROM Gameresults 
          WHERE Season = (SELECT Year FROM Evaluation) AND Member = 1 
          GROUP BY Name 
          HAVING COUNT(Name) >= 26
      )
    GROUP BY rs.Season, rs.Name 
)
SELECT 
    t.Season,
    t.Name,
    t.Pkt_Max21,
    b.Pkt_Min5,
    (t.Pkt_Max21 + b.Pkt_Min5) AS Total_Points
FROM top21_scores t
LEFT JOIN bottom5_scores b 
    ON t.Season = b.Season 
    AND t.Name = b.Name
ORDER BY Total_Points DESC;

代码说明 #

1. CTE定义：
   • top21_scores：保留原21次最佳成绩的查询逻辑，作为第一个子查询别名
   • bottom5_scores：保留原5次最差成绩的查询逻辑，作为第二个子查询别名
2. 关联逻辑：使用LEFT JOIN确保所有有21次最佳成绩记录的成员都能被显示（即使不满足>=26次记录的条件，此时Pkt_Min5会显示为NULL）；若只需要显示满足两个条件的成员，可改为INNER JOIN
3. 总积分计算：新增Total_Points字段直接求和两部分积分，按总积分降序排序方便选出赛季冠军

内容的提问来源于stack exchange，提问作者KD K