SQL Server中同一张sales表多次left join行转列丢失数据如何解决

问题原因 #

你之前的写法错误核心在于左表是筛选了shop='01'的子集，id=4没有01门店的销售数据，不会出现在左表中，后续关联的02、03门店的id=4数据自然就被整体丢弃了。

推荐解决方案：条件聚合（行转列标准写法） #

这种写法逻辑简单、性能更好，适配绝大多数SQL环境（MySQL、PostgreSQL、Hive、Spark SQL等都支持）：

SELECT
    id,
    MAX(CASE WHEN shop = '01' THEN sales ELSE 0.0 END) AS sales1,
    '01' AS shop1,
    MAX(CASE WHEN shop = '02' THEN sales ELSE 0.0 END) AS sales2,
    '02' AS shop2,
    MAX(CASE WHEN shop = '03' THEN sales ELSE 0.0 END) AS sales3,
    '03' AS shop3
FROM sales
GROUP BY id
ORDER BY id;

逻辑说明：按id分组后，对每个id的所有行做门店匹配，匹配到对应门店就取销售额，否则取0，用聚合函数取到唯一的有效值即可，门店编号是固定值直接写死即可，完全不需要多表关联。

若坚持使用JOIN写法的修正方案 #

需要先拿到所有唯一id的全集作为左表，保证所有id都不会丢失，再分别关联三个门店的销售数据：

SELECT
    t.id,
    COALESCE(a.sales, 0.0) AS sales1,
    '01' AS shop1,
    COALESCE(b.sales, 0.0) AS sales2,
    '02' AS shop2,
    COALESCE(c.sales, 0.0) AS sales3,
    '03' AS shop3
FROM (SELECT DISTINCT id FROM sales) t
LEFT JOIN sales a ON t.id = a.id AND a.shop = '01'
LEFT JOIN sales b ON t.id = b.id AND b.shop = '02'
LEFT JOIN sales c ON t.id = c.id AND c.shop = '03'
ORDER BY t.id;

逻辑说明：用所有id的全集当左表，关联不到的销售数据会返回空值，用COALESCE函数把空值替换为0即可得到预期结果。

内容的提问来源于stack exchange，提问作者Ernesto Chacón Pintor