编写WHERE子句含分支逻辑的SQL：指定code_id无结果时返回OTH默认值

需求说明 #

校验输入变量code_id是否存在匹配的MISC_FLAGS值，无匹配结果时返回CODE_ID = 'OTH'对应的默认值。 #

实现方案 #

方案1：跨数据库通用实现（兼容MySQL、PostgreSQL、Oracle、SQL Server等主流数据库） #

适用场景：支持单条/多条返回结果，无数据库语法依赖

SELECT MISC_FLAGS
FROM (
  -- 优先查询指定code_id，优先级标记为1
  SELECT MISC_FLAGS, 1 AS priority
  FROM systemcode 
  WHERE rec_type = 'C' 
    AND code_type = 'SAN' 
    AND CODE_ID = :input_code_id -- :input_code_id 替换为实际传入的code_id变量
  UNION ALL
  -- 兜底查询默认值，优先级标记为2
  SELECT MISC_FLAGS, 2 AS priority
  FROM systemcode 
  WHERE rec_type = 'C' 
    AND code_type = 'SAN' 
    AND CODE_ID = 'OTH'
) t
ORDER BY priority ASC
LIMIT 1;
-- Oracle、SQL Server 环境下如果LIMIT语法不兼容，替换为对应的分页逻辑：
-- Oracle：WHERE ROWNUM = 1
-- SQL Server：OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

逻辑说明：给两个查询分别设置优先级，指定code_id的优先级更高，排序后取第一条数据，只要指定code_id存在匹配结果就会优先返回，无匹配时自动返回默认值。 #

方案2：单值返回场景简化写法 #

适用场景：CODE_ID唯一，每个code_id仅对应一条MISC_FLAGS记录

SELECT COALESCE(
  (SELECT MISC_FLAGS FROM systemcode WHERE rec_type = 'C' AND code_type = 'SAN' AND CODE_ID = :input_code_id),
  (SELECT MISC_FLAGS FROM systemcode WHERE rec_type = 'C' AND code_type = 'SAN' AND CODE_ID = 'OTH')
) AS MISC_FLAGS

注意事项 #

• 建议提前给rec_type、code_type、CODE_ID三个字段加联合唯一索引，避免查询返回多条数据，同时提升查询性能
• 如果默认值OTH也可能不存在，可以根据业务需要在COALESCE中追加第三个参数作为兜底固定值

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