Microsoft SQL中为特定Lookup值声明替代值及查询集成方案

1. 基础实现：主码优先，别名兜底的查询 #

假设你的表结构如下：

• ItemAlias：存储主码与别名映射，字段为MainItemCode（主itemcode）、AlternateCode（别名）
• Pricing：存储定价数据，字段为ItemCode、Price

先声明要查询的目标itemcode列表（以表变量为例）：

DECLARE @TargetItemCodes TABLE (ItemCode VARCHAR(50));
INSERT INTO @TargetItemCodes VALUES ('ITEM001'), ('ITEM002'), ('ITEM003'); -- 替换为你的指定列表

执行查询，优先匹配主码，主码无数据时用别名兜底：

SELECT
    t.ItemCode AS MainItemCode,
    COALESCE(p_main.Price, p_alt.Price) AS FinalPrice
FROM @TargetItemCodes t
LEFT JOIN Pricing p_main ON t.ItemCode = p_main.ItemCode -- 先匹配主码定价
LEFT JOIN ItemAlias ia ON t.ItemCode = ia.MainItemCode -- 关联别名表
LEFT JOIN Pricing p_alt ON ia.AlternateCode = p_alt.ItemCode -- 用别名匹配定价
ORDER BY t.ItemCode;

COALESCE会优先取主码对应的价格，主码无数据时自动切换到别名价格；如果两者都无数据，会返回NULL，可根据业务需求用ISNULL(COALESCE(...), 0)替换为默认值。

2. 预声明匹配关系：封装可复用逻辑 #

如果要把这个匹配逻辑集成到复杂查询中，推荐两种封装方式：

方式一：标量值函数（适合简单场景） #

创建函数封装查找逻辑：

CREATE FUNCTION dbo.GetItemPrice(@MainItemCode VARCHAR(50))
RETURNS DECIMAL(18,2)
AS BEGIN
    DECLARE @Price DECIMAL(18,2);
    
    -- 先查主码价格
    SELECT @Price = Price FROM Pricing WHERE ItemCode = @MainItemCode;
    
    -- 主码无数据，查别名价格
    IF @Price IS NULL
        SELECT @Price = p.Price
        FROM ItemAlias ia
        JOIN Pricing p ON ia.AlternateCode = p.ItemCode
        WHERE ia.MainItemCode = @MainItemCode;
    
    RETURN @Price;
END;

在复杂查询中直接调用：

SELECT
    YourColumn1,
    YourColumn2,
    dbo.GetItemPrice(YourMainItemCodeColumn) AS ItemPrice
FROM YourComplexQueryTable
WHERE ...;

方式二：CTE/视图封装（适合大数据量场景，性能更优） #

标量函数在大数据量下可能存在性能瓶颈，推荐用CTE封装关联逻辑：

WITH ItemPriceLookup AS (
    -- 处理有别名的主码
    SELECT
        ia.MainItemCode,
        COALESCE(p_main.Price, p_alt.Price) AS Price
    FROM ItemAlias ia
    LEFT JOIN Pricing p_main ON ia.MainItemCode = p_main.ItemCode
    LEFT JOIN Pricing p_alt ON ia.AlternateCode = p_alt.ItemCode
    WHERE ia.MainItemCode IN (SELECT ItemCode FROM @TargetItemCodes)
    UNION ALL
    -- 处理无别名的主码
    SELECT
        ItemCode AS MainItemCode,
        Price
    FROM Pricing
    WHERE ItemCode IN (SELECT ItemCode FROM @TargetItemCodes)
        AND ItemCode NOT IN (SELECT MainItemCode FROM ItemAlias)
)
-- 直接将该CTE嵌入复杂查询
SELECT * 
FROM YourComplexQuery
JOIN ItemPriceLookup l ON YourComplexQuery.ItemCode = l.MainItemCode;

3. 注意事项 #

• 若一个主码对应多个别名，需根据业务规则处理重复数据（比如用TOP 1、取最大/最小价格）
• 为ItemAlias和Pricing表的ItemCode/AlternateCode字段创建索引，避免大数据量下性能问题
• 若需处理主码、别名都无数据的情况，可在COALESCE中添加默认值，例如COALESCE(p_main.Price, p_alt.Price, 0)

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