MySQL双表连接/透视表：实现指定两年零件库存数据展示

我来帮你搞定这个需求！你想要纯SQL实现任意两年库存数据的横向展示，同时保留所有零件——哪怕某个年份没有库存记录或者完全没数据，对吧？先说说你原查询的问题所在，再给你两个可行的方案。

原查询的问题分析 #

你原来的查询把年份筛选条件放在了WHERE子句里，这直接废掉了LEFT JOIN的效果！因为LEFT JOIN后，那些没有对应年份库存的零件，i1或i2的字段会是NULL，而WHERE i1.inventoryYear = 2016会直接过滤掉i1为NULL的行，相当于把LEFT JOIN变成了INNER JOIN，这就是为什么只有两年都有数据的零件才会被查出来。

方案一：调整LEFT JOIN的ON条件（最直观的两年场景方案） #

把年份筛选条件移到LEFT JOIN的ON子句里，这样就能保证所有零件都被保留，没有对应年份库存的字段会显示NULL，你还可以用函数把NULL转换成更友好的默认值：

SELECT 
    p.pmkPart,
    p.partNumber,
    -- 2016年库存信息，无数据则显示0或自定义文本
    COALESCE(i1.inventoryNum, 0) AS inventoryNum_2016,
    COALESCE(i1.inventoryR, '无') AS inventoryR_2016,
    COALESCE(i1.inventoryC, '无') AS inventoryC_2016,
    -- 2017年库存信息
    COALESCE(i2.inventoryNum, 0) AS inventoryNum_2017,
    COALESCE(i2.inventoryR, '无') AS inventoryR_2017,
    COALESCE(i2.inventoryC, '无') AS inventoryC_2017
FROM part AS p
LEFT JOIN inventory AS i1 
    ON p.pmkPart = i1.fnkPart AND i1.inventoryYear = 2016  -- 年份条件放在JOIN时筛选
LEFT JOIN inventory AS i2 
    ON p.pmkPart = i2.fnkPart AND i2.inventoryYear = 2017
ORDER BY p.pmkPart;

说明： #

• COALESCE函数可以把NULL转换成你想要的默认值，比如用0表示无库存数量，用“无”表示无位置信息，不需要转换的话直接用i1.inventoryNum即可。
• 要切换任意两年组合，直接替换SQL里的2016和2017数值就行，操作非常简单。

方案二：CASE+聚合函数（类透视表方案，扩展性更强） #

如果以后可能需要扩展到更多年份，这种方式更灵活。用CASE语句提取对应年份的字段值，再配合MAX聚合（因为每个零件对应年份最多一条记录，MAX会忽略NULL取有效数值）：

SELECT 
    p.pmkPart,
    p.partNumber,
    MAX(CASE WHEN i.inventoryYear = 2016 THEN i.inventoryNum END) AS inventoryNum_2016,
    MAX(CASE WHEN i.inventoryYear = 2016 THEN i.inventoryR END) AS inventoryR_2016,
    MAX(CASE WHEN i.inventoryYear = 2016 THEN i.inventoryC END) AS inventoryC_2016,
    MAX(CASE WHEN i.inventoryYear = 2017 THEN i.inventoryNum END) AS inventoryNum_2017,
    MAX(CASE WHEN i.inventoryYear = 2017 THEN i.inventoryR END) AS inventoryR_2017,
    MAX(CASE WHEN i.inventoryYear = 2017 THEN i.inventoryC END) AS inventoryC_2017
FROM part AS p
LEFT JOIN inventory AS i 
    ON p.pmkPart = i.fnkPart
WHERE i.inventoryYear IN (2016, 2017) OR i.inventoryYear IS NULL  -- 保留无库存记录的零件
GROUP BY p.pmkPart, p.partNumber
ORDER BY p.pmkPart;

说明： #

• WHERE子句里的OR i.inventoryYear IS NULL是关键，用来保留那些完全没有库存记录的零件（LEFT JOIN后i的所有字段都是NULL）。
• 如果要加第三年，只要新增一组MAX(CASE...)语句就行，扩展性拉满。

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