SQL多表关联查询：实现MODIFIER列动态取数需求

你的查询存在几个核心问题：

• 首个RIGHT JOIN [dbo].[ITEM] I ON I.RECORD_KEY = I.RECORD_KEY属于无效自关联，条件恒真会导致数据异常
• MODIFIER列仅取M.NAME，未处理M.ITEM_RECORD_KEY关联ITEM表的场景
• RIGHT JOIN的顺序可能过滤掉部分需要展示的数据

修改后的查询 #

SELECT
    I.NAME AS 'ITEM NAME',
    ML.NAME AS 'MODIFIER LIST',
    MG.NAME AS 'MODIFIER GROUP',
    -- 优先取关联ITEM的名称，无关联则用MODIFIER自身名称
    COALESCE(ITEM_MOD.NAME, M.NAME) AS 'MODIFIER',
    FORMAT(CAST(M.UPCHARGE_EXPRESSION AS numeric), 'c', 'en-us') AS 'UPCHARGE'
FROM 
    [dbo].[ITEM] I
LEFT JOIN 
    [dbo].[ITEM_MODIFIER] IM ON IM.ITEM_RECORD_KEY = I.RECORD_KEY
LEFT JOIN 
    [dbo].[MODIFIER_LIST] ML ON ML.RECORD_KEY = IM.MODIFIER_LIST_RECORD_KEY
LEFT JOIN 
    [dbo].[MODIFIER_GROUP] MG ON MG.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
LEFT JOIN 
    [dbo].[MODIFIER] M ON M.MODIFIER_GROUP_RECORD_KEY = MG.RECORD_KEY
-- 新增关联：通过MODIFIER的ITEM_RECORD_KEY获取对应ITEM名称
LEFT JOIN 
    [dbo].[ITEM] ITEM_MOD ON ITEM_MOD.RECORD_KEY = M.ITEM_RECORD_KEY
GROUP BY 
    I.NAME, ML.NAME, MG.NAME, COALESCE(ITEM_MOD.NAME, M.NAME), M.UPCHARGE_EXPRESSION
ORDER BY 
    I.NAME, ML.NAME, MG.NAME, COALESCE(ITEM_MOD.NAME, M.NAME)

关键修改说明 #

• 移除无效关联：删掉了原查询中无意义的自关联语句
• 动态取值逻辑：用COALESCE()函数实现优先取关联ITEM的名称，若不存在则 fallback 到MODIFIER自身名称
• 调整JOIN类型：将RIGHT JOIN改为LEFT JOIN，避免主表数据被意外过滤
• 同步分组排序：GROUP BY和ORDER BY中替换为COALESCE()表达式，保证与SELECT列逻辑一致

场景扩展：确保所有MODIFIER都被返回 #

如果需要展示所有修饰符数据（即使无关联ITEM），可以将MODIFIER设为主表，反向关联其他表：

SELECT
    I.NAME AS 'ITEM NAME',
    ML.NAME AS 'MODIFIER LIST',
    MG.NAME AS 'MODIFIER GROUP',
    COALESCE(ITEM_MOD.NAME, M.NAME) AS 'MODIFIER',
    FORMAT(CAST(M.UPCHARGE_EXPRESSION AS numeric), 'c', 'en-us') AS 'UPCHARGE'
FROM 
    [dbo].[MODIFIER] M
LEFT JOIN 
    [dbo].[MODIFIER_GROUP] MG ON MG.RECORD_KEY = M.MODIFIER_GROUP_RECORD_KEY
LEFT JOIN 
    [dbo].[MODIFIER_LIST] ML ON ML.RECORD_KEY = MG.MODIFIER_LIST_RECORD_KEY
LEFT JOIN 
    [dbo].[ITEM_MODIFIER] IM ON IM.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
LEFT JOIN 
    [dbo].[ITEM] I ON I.RECORD_KEY = IM.ITEM_RECORD_KEY
LEFT JOIN 
    [dbo].[ITEM] ITEM_MOD ON ITEM_MOD.RECORD_KEY = M.ITEM_RECORD_KEY
GROUP BY 
    I.NAME, ML.NAME, MG.NAME, COALESCE(ITEM_MOD.NAME, M.NAME), M.UPCHARGE_EXPRESSION
ORDER BY 
    I.NAME, ML.NAME, MG.NAME, COALESCE(ITEM_MOD.NAME, M.NAME)

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