如何在SQL的Full Join结果中合并LOCATION与SKU列？

不需要创建维护成本高的第三方表，直接用SQL内置函数或UNION聚合就能实现单列合并，同时保留全量记录。

方法一：用COALESCE()直接合并列 #

Full Join后，同一行的SAGE和WMS对应列必然有一方非空（或双方都有值），COALESCE()会依次取第一个非空值，完美适配合并需求。

假设你的原CTE及Full Join代码如下：

WITH SAGE_INV AS (
    SELECT SKU AS SAGE_SKU, LOCATION AS SAGE_LOCATION, QTY AS SAGE_QTY
    FROM SAGE_INVENTORY
),
WMS_INV AS (
    SELECT SKU AS WMS_SKU, LOCATION AS WMS_LOCATION, QTY AS WMS_QTY
    FROM WMS_INVENTORY
)
SELECT 
    SAGE_SKU, WMS_SKU,
    SAGE_LOCATION, WMS_LOCATION,
    SAGE_QTY, WMS_QTY
FROM SAGE_INV
FULL JOIN WMS_INV 
    ON SAGE_INV.SAGE_SKU = WMS_INV.WMS_SKU 
    AND SAGE_INV.SAGE_LOCATION = WMS_INV.WMS_LOCATION

只需要修改SELECT部分，用COALESCE()合并列：

WITH SAGE_INV AS (
    SELECT SKU AS SAGE_SKU, LOCATION AS SAGE_LOCATION, QTY AS SAGE_QTY
    FROM SAGE_INVENTORY
),
WMS_INV AS (
    SELECT SKU AS WMS_SKU, LOCATION AS WMS_LOCATION, QTY AS WMS_QTY
    FROM WMS_INVENTORY
)
SELECT 
    COALESCE(SAGE_SKU, WMS_SKU) AS SKU,
    COALESCE(SAGE_LOCATION, WMS_LOCATION) AS LOCATION,
    SAGE_QTY,
    WMS_QTY
FROM SAGE_INV
FULL JOIN WMS_INV 
    ON SAGE_INV.SAGE_SKU = WMS_INV.WMS_SKU 
    AND SAGE_INV.SAGE_LOCATION = WMS_INV.WMS_LOCATION

特殊场景处理 #

如果存在同一SKU+LOCATION组合下，两边编码不一致的情况：

• 要保留两边信息：用CONCAT(COALESCE(SAGE_SKU, ''), ' / ', COALESCE(WMS_SKU, '')) AS SKU拼接显示
• 要优先取某一方值：调整COALESCE()参数顺序，比如COALESCE(WMS_SKU, SAGE_SKU)优先用WMS数据

方法二：UNION ALL + 聚合（适合需合并库存数量的场景） #

如果Full Join后出现重复行，或者需要聚合两边的库存数值，可以先把两个表的记录合并，再按SKU和LOCATION分组聚合：

WITH SAGE_INV AS (
    SELECT SKU, LOCATION, QTY AS SAGE_QTY, NULL AS WMS_QTY
    FROM SAGE_INVENTORY
),
WMS_INV AS (
    SELECT SKU, LOCATION, NULL AS SAGE_QTY, QTY AS WMS_QTY
    FROM WMS_INVENTORY
),
COMBINED AS (
    SELECT * FROM SAGE_INV
    UNION ALL
    SELECT * FROM WMS_INV
)
SELECT 
    SKU,
    LOCATION,
    MAX(SAGE_QTY) AS SAGE_QTY,
    MAX(WMS_QTY) AS WMS_QTY
FROM COMBINED
GROUP BY SKU, LOCATION

这个方法自动合并SKU和LOCATION列，还能把同一组合下的库存数值聚合展示。

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