SQL中使用标记位进行条件判断的场景、原因与方法

要理解该问题，我们首先来看一个真实业务场景示例：假设我们于2020年开设了一家冰淇淋店，需要统计销量最高的饮品。2022年时，我们需要判断热饮的销量、销售额是否达到预期，以此决定未来是否仅售卖冷饮。

为简化分析，我们假设冰淇淋等非饮品类商品已单独分类统计，无需纳入本次分析范围。现有一张结构非常简单的数据库表drinks，表中已预先聚合了各商品按年统计的累计销量与销售额，基础查询语句如下：

SELECT name,quantity,amount,year
FROM drinks
ORDER BY name,year;

基础查询返回的样例数据如下：

如果仅有2款商品，直接对比咖啡与可乐的销量、销售额即可。但真实业务场景下还会售卖espresso、cappuccino、矿泉水、sprite等更多饮品，初期最容易想到的方案是直接通过商品名称硬编码判断条件：

• 热饮判断条件：name IN('coffee','cappuccino','espresso')
• 冷饮判断条件：name IN('coke','water','sprite')

但执行上述条件的查询后结果并不准确：2021年门店新增了茶饮类商品售卖，因此需要修改热饮判断条件为：

name IN('coffee','cappuccino','espresso') 
OR name LIKE '%tea%'

调整后的条件在2020、2021年的统计中结果准确，但2022年的统计结果又出现了错误。经排查，2022年门店新增了冰茶商品，原有条件会将冰茶误归类为热饮，导致统计失准，因此需要再次修改判断逻辑，最终拼凑出的完整查询语句如下：

SELECT 
SUM(CASE WHEN name IN('coffee','cappuccino','espresso') 
OR (name LIKE '%tea%' AND name NOT LIKE '%ice%')
THEN quantity ELSE 0 END) AS quantityHotDrinks,
SUM(CASE WHEN name IN('coffee','cappuccino','espresso') 
OR (name LIKE '%tea%' AND name NOT LIKE '%ice%')
THEN amount ELSE 0 END) AS amountHotDrinks,
SUM(CASE WHEN name IN('coke','water','sprite') 
OR name LIKE '%ice tea%'
THEN quantity ELSE 0 END) AS quantityColdDrinks,
SUM(CASE WHEN name IN('coke','water','sprite') 
OR name LIKE '%ice tea%'
THEN amount ELSE 0 END) AS amountColdDrinks,
year
FROM drinks
GROUP BY year

这种写法非常冗长、可读性差，同时存在极高的出错风险：如果仅做临时查询核对，风险尚且可控，但如果要基于统计结果做出商品上下架的经营决策，就必须保证数据的准确性。例如未来可乐品类拆分出coke zero、coke light、normal coke三个SKU，就需要再次修改判断条件。条件逻辑越复杂，结果出错的概率越高，错误排查的难度也越大。

核心原则：永远不要在统计逻辑中硬编码基于商品名称的分类规则，要把分类属性和统计逻辑解耦。

商品名称是面向用户的展示字段，不是结构化的分类标记，随时可能因为新品上线、运营调整改名出现规则漏洞，靠IN、LIKE打补丁的写法本质是在给不可控的文本内容补漏洞，迟早出问题。常规落地方案有两种，按优先级选即可：

• 方案1：新增独立的商品分类映射表
  新建一张drink_category映射表，仅保留两个核心字段：name（商品名称，和drinks表的商品名一一对应）、category（分类值，固定填hot/cold即可）。每次上新SKU时，先在这张表里维护好分类映射，再做数据统计。
  统计时直接关联映射表做聚合即可，完全不需要写复杂的条件判断：

  SELECT 
    SUM(CASE WHEN dc.category = 'hot' THEN d.quantity ELSE 0 END) AS quantityHotDrinks,
    SUM(CASE WHEN dc.category = 'hot' THEN d.amount ELSE 0 END) AS amountHotDrinks,
    SUM(CASE WHEN dc.category = 'cold' THEN d.quantity ELSE 0 END) AS quantityColdDrinks,
    SUM(CASE WHEN dc.category = 'cold' THEN d.amount ELSE 0 END) AS amountColdDrinks,
    d.year
  FROM drinks d
  LEFT JOIN drink_category dc ON d.name = dc.name
  GROUP BY d.year
  

  这种方案维护成本最低，分类规则完全透明，排查分类错误只需要核对映射表即可，不会出现字符串模糊匹配的误判问题。
• 方案2：在drinks主表直接加分类字段
  如果不想额外建关联表，可以直接在drinks表新增category字段，写入每一条商品销售记录时就提前标记好是热饮还是冷饮，统计时直接按字段聚合即可，连JOIN操作都不需要，查询效率更高，适合数据量极大的场景。

补充提示：如果后续分类维度变多（比如还要分含糖/无糖、含咖啡因/不含咖啡因），直接在映射表或者主表加对应分类字段即可，统计逻辑只需要改一次聚合维度，不需要反复拼凑字符串判断条件，扩展性极强。

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