SQL实现两张产品使用表按ID+月份合并为单行多使用列的问题

• 关联逻辑缺陷：使用LEFT JOIN以product1_base为左表，若存在某ID某月仅在product2_base有数据的情况，该条记录会直接被遗漏，无法纳入结果。
• 字段判断逻辑错误：CASE语句优先匹配prod1_usage非空的情况，同一行中prod2_usage的数值永远不会被识别，后续透视自然无法正常展示product2的使用量。

方案1：直接关联生成目标宽表（适用于产品数量固定的场景） #

无需额外透视，直接通过全外连接保留两边所有的月度ID记录，分别取两个产品的使用量字段即可：

SELECT
    COALESCE(a.month, b.month) AS month,
    COALESCE(a.id, b.id) AS id,
    COALESCE(a.prod1_usage, 0) AS prod1_usage,
    COALESCE(b.prod2_usage, 0) AS prod2_usage
FROM product1_base a
FULL OUTER JOIN product2_base b 
ON a.id = b.id AND a.month = b.month
ORDER BY month, id;

说明：如果不需要把空值填充为0，直接去掉COALESCE函数包裹即可；若两张表的月度ID记录完全一一对应，把FULL OUTER JOIN替换为INNER JOIN也能得到正确结果。

方案2：合并后透视生成宽表（适用于产品数量多、后续需要扩展的场景） #

先将两张表的使用数据合并为统一结构的明细表，再通过分组聚合实现行转列：

WITH all_usage AS (
    -- 合并两个产品的使用记录为统一结构
    SELECT month, id, prod1_usage AS usage, 'product 1' AS prod_type FROM product1_base
    UNION ALL
    SELECT month, id, prod2_usage AS usage, 'product 2' AS prod_type FROM product2_base
)
-- 按月份、ID分组透视生成宽表
SELECT
    month,
    id,
    MAX(CASE WHEN prod_type = 'product 1' THEN usage ELSE 0 END) AS prod1_usage,
    MAX(CASE WHEN prod_type = 'product 2' THEN usage ELSE 0 END) AS prod2_usage
FROM all_usage
GROUP BY month, id
ORDER BY month, id;

内容的提问来源于stack exchange，提问作者E.A.