如何在SQL查询开头声明ItemCode别名并实现双值匹配查询？

核心思路是用**CTE（公共表表达式）**在查询开头直接声明主ItemCode和AlternateCode的映射关系，不用单独维护物理映射表，然后通过关联逻辑优先匹配主码，主码无数据时自动匹配别名，最终返回主码和对应价格。

示例场景 #

假设我们有以下需求：

• 临时映射规则：
  • 主ItemCode A 对应别名 A1、A2
  • 主ItemCode B 对应别名 B1
• pricing 表现有数据：

  ItemCode	Price
  A1	10
  B	20
  A2	12

• 期望结果：查询主码A、B时，返回A对应价格（可指定取别名中的最低/最高价），B对应20；无匹配数据的主码自动过滤。

具体SQL实现 #

WITH item_mappings AS (
    -- 直接在这里写死主码和别名的对应关系，不用单独建表
    SELECT 'A' AS main_item_code, 'A1' AS alternate_code
    UNION ALL
    SELECT 'A' AS main_item_code, 'A2' AS alternate_code
    UNION ALL
    SELECT 'B' AS main_item_code, 'B1' AS alternate_code
)
SELECT 
    -- 优先取主码，主码不存在则用别名对应的主码
    COALESCE(p_main.main_item_code, p_alt.main_item_code) AS main_item_code,
    -- 优先取主码的价格，主码无数据则取别名的价格
    COALESCE(p_main.price, p_alt.price) AS price
FROM (
    -- 这里放你要查询的主ItemCode列表
    SELECT 'A' AS target_item
    UNION ALL
    SELECT 'B' AS target_item
) AS target_list
-- 第一步：先匹配主ItemCode的价格
LEFT JOIN (
    SELECT im.main_item_code, pr.price
    FROM item_mappings im
    JOIN pricing pr ON im.main_item_code = pr.ItemCode
) AS p_main ON target_list.target_item = p_main.main_item_code
-- 第二步：主ItemCode没匹配到的话，匹配别名的价格
LEFT JOIN (
    -- 如果一个主码对应多个别名有价格，这里可以用MIN/MAX取固定值，比如取最低价
    SELECT im.main_item_code, MIN(pr.price) AS price
    FROM item_mappings im
    JOIN pricing pr ON im.alternate_code = pr.ItemCode
    GROUP BY im.main_item_code
) AS p_alt ON target_list.target_item = p_alt.main_item_code
-- 过滤掉没有价格的无效条目
WHERE COALESCE(p_main.price, p_alt.price) IS NOT NULL;

逻辑说明 #

1. 临时映射表：通过WITH创建的item_mappings是临时的，每次查询可以直接修改里面的UNION ALL条目来更新映射关系，不用维护单独的物理表。
2. 优先级匹配：先尝试用主ItemCode关联pricing表拿价格，拿不到再用别名去关联。
3. 多别名处理：如果一个主码对应多个别名都有价格，可在p_alt子查询里用聚合函数（MIN/MAX）指定取哪个价格，避免返回多条重复主码的结果。

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