多表CASE JOIN查询问题：合并两表统计结果至同一行

需要从两个不同数据库表获取Count和Average统计值并合并输出，尝试用CASE语句融合记录后未得到预期结果——Pole_B团队同时存在于两个表中，希望将该团队在两表中的统计值展示在同一行。

当前错误结果 #

team_a  team_b  pts_a   pts_b   avg_c   pts_d   pts_e   avg_f
Pole_A  Pole_B      12      12      3.7     12      12  3.0 
Pole_A  Pole_C      12      12      3.7     12      12  3.0
Pole_A  Pole_D      12      12      3.7     12      12  3.8
Pole_B  Pole_B      16      16      2.8     16      16  3.0
Pole_B  Pole_C      16      16      2.8     16      16  3.0
Pole_B  Pole_D      16      16      2.8     16      16  3.8

期望正确结果 #

team_ab pts_a   pts_b   avg_c   pts_d   pts_e   avg_f
Pole_A      3       3       3,7     0       0   0
Pole_B      4       4       2,8     4       4   3,0
Pole_C      0       0       0       4       4   3,0
Pole_D      0       0       0       4       4   3,8

当前查询语句 #

SELECT a.team_a, b.team_b,
   COUNT(CASE WHEN a.pts_a !=99 THEN 1 ELSE 0 END) AS pts_a,
   COUNT(CASE WHEN a.pts_b !=99 THEN 1 ELSE 0 END) AS pts_b,
   ROUND(AVG(CASE WHEN a.avg_c IN(1,2,3,4,5) THEN avg_c END),1) AS avg_c, 
   COUNT(CASE WHEN b.pts_d !=99 THEN 1 ELSE 0 END) AS pts_d,
   COUNT(CASE WHEN b.pts_e !=99 THEN 1 ELSE 0 END) AS pts_e,
   ROUND(AVG(CASE WHEN b.avg_f IN(1,2,3,4,5) THEN avg_f END),1) AS avg_f
FROM tbla a
LEFT JOIN tblb b ON a.week_a = b.week_b
GROUP BY team_a, team_b
ORDER BY team_a ASC;

解决方案 #

问题根源是直接通过week关联两张表后分组，导致不同团队的周数据匹配产生笛卡尔积，生成多余行。正确做法是先分别统计单表的团队指标，再通过团队名关联合并：

WITH stats_a AS (
    SELECT 
        team_a AS team_ab,
        COUNT(CASE WHEN pts_a != 99 THEN 1 END) AS pts_a,
        COUNT(CASE WHEN pts_b != 99 THEN 1 END) AS pts_b,
        COALESCE(ROUND(AVG(CASE WHEN avg_c IN(1,2,3,4,5) THEN avg_c END), 1), 0) AS avg_c
    FROM tbla
    GROUP BY team_a
),
stats_b AS (
    SELECT 
        team_b AS team_ab,
        COUNT(CASE WHEN pts_d != 99 THEN 1 END) AS pts_d,
        COUNT(CASE WHEN pts_e != 99 THEN 1 END) AS pts_e,
        COALESCE(ROUND(AVG(CASE WHEN avg_f IN(1,2,3,4,5) THEN avg_f END), 1), 0) AS avg_f
    FROM tblb
    GROUP BY team_b
)
SELECT 
    COALESCE(a.team_ab, b.team_ab) AS team_ab,
    COALESCE(a.pts_a, 0) AS pts_a,
    COALESCE(a.pts_b, 0) AS pts_b,
    COALESCE(a.avg_c, 0) AS avg_c,
    COALESCE(b.pts_d, 0) AS pts_d,
    COALESCE(b.pts_e, 0) AS pts_e,
    COALESCE(b.avg_f, 0) AS avg_f
FROM stats_a a
FULL OUTER JOIN stats_b b ON a.team_ab = b.team_ab
ORDER BY team_ab;

关键说明 #

1. 单表预统计：用CTE分别计算tbla和tblb中每个团队的指标，避免跨表关联导致的笛卡尔积。
2. 全外连接：使用FULL OUTER JOIN确保所有团队（仅在A表、仅在B表、同时在两表）都被纳入结果。
3. 空值处理：用COALESCE将NULL值替换为0，匹配预期结果的格式要求。

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