SQL LEFT JOIN同ID多记录数据覆盖 如何生成匹配计数列

原写法问题 #

你当前的SQL存在两个核心问题，无法得到预期结果：

• 语法错误：左关联关键字缺失，LEFT Table2应为LEFT JOIN Table2
• 逻辑缺失：直接关联两张表会将表2同ID下的多条匹配记录拆分为独立行返回，不会自动按pet类型做分栏聚合，也不会自动计算总匹配次数，且未对空匹配结果做0值填充。

实现思路 #

先对表2按ID维度做分组聚合，提前计算好每个ID对应的宠物总数量、各类型宠物的计数，再将聚合后的结果集与表1做左关联，对关联产生的空值统一填充为0即可。这种写法比先关联再聚合的执行效率更高，也不会产生重复行问题。

可直接运行的代码 #

以下代码兼容MySQL、PostgreSQL、SQL Server等主流数据库：

SELECT
    t1.ID,
    t1.value AS val,
    COALESCE(t2_stat.count_pets, 0) AS count_pets,
    COALESCE(t2_stat.cat, 0) AS cat,
    COALESCE(t2_stat.dog, 0) AS dog,
    COALESCE(t2_stat.fish, 0) AS fish
FROM Table1 t1
LEFT JOIN (
    SELECT
        ID,
        COUNT(*) AS count_pets,
        SUM(CASE WHEN pet = 'cat' THEN 1 ELSE 0 END) AS cat,
        SUM(CASE WHEN pet = 'dog' THEN 1 ELSE 0 END) AS dog,
        SUM(CASE WHEN pet = 'fish' THEN 1 ELSE 0 END) AS fish
    FROM Table2
    GROUP BY ID
) t2_stat ON t1.ID = t2_stat.ID;

补充说明 #

• 按你给出的示例数据，执行上述代码会返回如下结果，完全匹配预期：

• 如果你需要额外追加同ID下最后一条匹配的pet记录，可以在表2的子查询中用窗口函数取数：新增排序字段（比如记录创建时间、自增ID），通过ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 排序字段 DESC)筛选排名为1的记录，再和聚合结果关联即可。
• COALESCE函数的作用是将左关联未匹配到数据时产生的NULL值替换为0，符合无匹配记0的要求。

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