在WHERE子句中用CASE过滤记录：求更优SQL实现方案

问题背景 #

我有一个包含14条记录的输入表（见配图），希望筛选得到输出表（见配图）中的记录。目前我用CASE语句写了如下SQL实现需求，但想知道有没有更简洁高效的实现方式，恳请指教。

原SQL语句 #

Select * from Table WHERE

((Case WHEN Type = 'BLN' THEN AMOUNT end ) > 70000

OR (Case WHEN Type = 'CRM' THEN AMOUNT end) > 200000

OR (CASE WHEN Type = 'CY' THEN AMOUNT END) > 90000

OR

(CASE WHEN Type = 'DAN' THEN AMOUNT END) > 450000

OR (CASE WHEN Type = 'EAN' THEN AMOUNT END) > 100000

OR (CASE WHEN Type = 'EPL' THEN AMOUNT END) > 70000

OR (CASE WHEN Type = 'FPL' THEN AMOUNT END) > 30000

OR (CASE WHEN Type NOT IN ('BLN','CRM','CY','DAN','EAN','EPL','FPL') THEN AMOUNT END) > 200000

)

优化方案 #

方案一：直接用Type与Amount的组合条件 #

将CASE语句替换为Type = 'XXX' AND AMOUNT > YYY的OR组合，逻辑更直观，数据库也更容易优化执行计划：

SELECT * 
FROM Table 
WHERE 
    (Type = 'BLN' AND AMOUNT > 70000)
    OR (Type = 'CRM' AND AMOUNT > 200000)
    OR (Type = 'CY' AND AMOUNT > 90000)
    OR (Type = 'DAN' AND AMOUNT > 450000)
    OR (Type = 'EAN' AND AMOUNT > 100000)
    OR (Type = 'EPL' AND AMOUNT > 70000)
    OR (Type = 'FPL' AND AMOUNT > 30000)
    OR (Type NOT IN ('BLN','CRM','CY','DAN','EAN','EPL','FPL') AND AMOUNT > 200000)

优势：

• 逻辑清晰，可读性强，后续维护更简单
• 若Type和AMOUNT存在联合索引，数据库能直接命中索引，执行效率优于原CASE语句写法

方案二：用配置表维护阈值（适合规则频繁变动场景） #

如果这类Type对应的阈值需要经常修改或新增，可以创建一个独立的配置表来维护规则，避免频繁修改查询语句：

1. 创建并填充阈值配置表（仅需初始化一次）：

CREATE TABLE type_threshold (
    type VARCHAR(10) PRIMARY KEY,
    min_amount DECIMAL(18,2) NOT NULL
);

INSERT INTO type_threshold VALUES
('BLN', 70000),
('CRM', 200000),
('CY', 90000),
('DAN', 450000),
('EAN', 100000),
('EPL', 70000),
('FPL', 30000),
('OTHER', 200000); -- 其他Type的默认阈值

2. 使用JOIN关联查询：

SELECT t.*
FROM Table t
LEFT JOIN type_threshold tt 
    ON t.Type = tt.type
WHERE 
    t.AMOUNT > COALESCE(tt.min_amount, (SELECT min_amount FROM type_threshold WHERE type = 'OTHER'));

优势：

• 阈值规则集中管理，修改或新增规则时只需更新配置表，无需改动查询语句
• 扩展性强，适合业务规则频繁变化的场景

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