如何将Excel反向查找表逻辑迁移至SQL，替代复杂计算列？

不用写超长难维护的计算列公式，直接复用你的查找表结构，通过关联查询+规则优先级实现，后续改规则只需动查找表，不用改SQL。

核心思路 #

把查找表的匹配规则转成SQL关联条件，同时给规则加优先级，确保精准规则（比如Apple-Apple>=5）比通用规则（比如*-*<5）先匹配，最终取第一个符合条件的规则结果。

代码实现 #

假设你的查找表叫fruit_lookup，数据表叫fruit_data，用以下SQL即可得到计算列：

方法1：用CROSS APPLY取首个匹配规则（推荐，逻辑直观） #

SELECT 
    d.owned_fruit,
    d.desired_fruit,
    d.fruit_stock,
    l.designation
FROM fruit_data d
CROSS APPLY (
    -- 取第一个符合所有匹配条件的规则
    SELECT TOP 1 designation
    FROM fruit_lookup l
    WHERE
        -- 处理owned_fruit的匹配：通配符*、不等号<>、精准匹配
        (l.owned_fruit = '*' 
         OR (LEFT(l.owned_fruit, 2) = '<>' AND d.owned_fruit != SUBSTRING(l.owned_fruit, 3))
         OR l.owned_fruit = d.owned_fruit)
        AND
        -- 处理desired_fruit的匹配逻辑同上
        (l.desired_fruit = '*' 
         OR (LEFT(l.desired_fruit, 2) = '<>' AND d.desired_fruit != SUBSTRING(l.desired_fruit, 3))
         OR l.desired_fruit = d.desired_fruit)
        AND
        -- 处理fruit_stock的数值比较：>=5、<5这类规则
        (
            CASE 
                WHEN l.fruit_stock LIKE '>=%' THEN d.fruit_stock >= CAST(SUBSTRING(l.fruit_stock, 3) AS INT)
                WHEN l.fruit_stock LIKE '<%' THEN d.fruit_stock < CAST(SUBSTRING(l.fruit_stock, 2) AS INT)
                -- 后续新增其他比较规则（比如<=10、=3）直接加CASE分支即可
                ELSE FALSE
            END
        )
    -- 排序确保精准规则优先匹配
    ORDER BY 
        CASE WHEN l.owned_fruit != '*' THEN 1 ELSE 2 END,
        CASE WHEN l.desired_fruit != '*' THEN 1 ELSE 2 END,
        CASE WHEN l.fruit_stock != '<5' THEN 1 ELSE 2 END
) l

方法2：用窗口函数取优先级最高的匹配结果 #

如果你的SQL方言支持窗口函数（比如SQL Server、PostgreSQL、MySQL 8+），也可以用这种方式：

WITH ranked_lookup AS (
    -- 给查找表的规则加优先级，数值越小优先级越高
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 
            CASE WHEN owned_fruit != '*' THEN 1 ELSE 2 END,
            CASE WHEN desired_fruit != '*' THEN 1 ELSE 2 END,
            CASE WHEN fruit_stock != '<5' THEN 1 ELSE 2 END
        ) AS priority
    FROM fruit_lookup
)
SELECT 
    d.owned_fruit,
    d.desired_fruit,
    d.fruit_stock,
    -- 取每个数据行对应的最高优先级匹配结果
    FIRST_VALUE(l.designation) OVER (
        PARTITION BY d.owned_fruit, d.desired_fruit, d.fruit_stock
        ORDER BY l.priority
    ) AS designation
FROM fruit_data d
LEFT JOIN ranked_lookup l ON 
    (l.owned_fruit = '*' 
     OR (LEFT(l.owned_fruit, 2) = '<>' AND d.owned_fruit != SUBSTRING(l.owned_fruit, 3))
     OR l.owned_fruit = d.owned_fruit)
    AND
    (l.desired_fruit = '*' 
     OR (LEFT(l.desired_fruit, 2) = '<>' AND d.desired_fruit != SUBSTRING(l.desired_fruit, 3))
     OR l.desired_fruit = d.desired_fruit)
    AND
    (
        CASE 
            WHEN l.fruit_stock LIKE '>=%' THEN d.fruit_stock >= CAST(SUBSTRING(l.fruit_stock, 3) AS INT)
            WHEN l.fruit_stock LIKE '<%' THEN d.fruit_stock < CAST(SUBSTRING(l.fruit_stock, 2) AS INT)
            ELSE FALSE
        END
    )

后续维护 #

• 新增/修改规则：直接在fruit_lookup表中添加或修改行，不用改动SQL代码
• 扩展匹配类型：比如要加<=10或者=2这类规则，只需在CASE语句中新增对应的判断分支即可

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