如何优化含重复条件的CASE WHEN语句？嵌套CASE WHEN是否可行？

嵌套子CASE WHEN完全可行，而且是简化这类重复条件的常用手段，下面给你两种实用的优化方案：

一、嵌套子CASE简化现有代码 #

把重复的c.clm_its_host_cd IN ('HOST','JAACL')条件抽出来作为外层判断，内层只处理不同的分支逻辑，代码会更简洁，可读性也更高：

, CASE
  -- 抽离重复的host条件作为外层判断
  WHEN c.clm_its_host_cd IN ('HOST','JAACL') THEN
    CASE
      WHEN c.clm_sor_cd <> '808' THEN ''
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('040','540','041','044','046') THEN 'CA'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('051','551','050','550','052') THEN 'CO'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('266','766','265','765','267') THEN 'NV'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('131','631','132','130','630') THEN 'IN'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('161','661','162','160','660') THEN 'KY'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('748','749','241','741','248') THEN 'MO'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('330','830','329','332','834') THEN 'OH'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('458','454','954','450','950') THEN 'WI'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('425','925','427','429') THEN 'VA'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('254','246','746','308') THEN 'NY'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('102','602','101','601','103') AND ca.prcsg_unit_id IN ('8Y') THEN 'GA'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('062','562','063','563','064') AND ca.prcsg_unit_id IN ('7W','8M') THEN 'CT'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('182','682','183','685','185') AND ca.prcsg_unit_id IN ('7Y') THEN 'ME'
      WHEN Substr(c.clm_its_sccf_nbr,1,3) IN ('271','771','272','769','269') AND ca.prcsg_unit_id IN ('8D') THEN 'NH'
    END
  -- 以下保留原有的其他分支逻辑
  WHEN cl.clm_sor_cd = '808' AND clp.prov_st_cd IN ('CA', 'CO', 'NV', 'IN', 'KY', 'OH', 'MO', 'WI', 'NH', 'ME', 'CT', 'NY', 'VA', 'GA') THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,4) = 'SPEC' THEN 'GA' 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,4) = 'HOSP' THEN 'GA' 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,2) IN ('CT','ME','NY','VA','NH','MO','OH','KY','WI') THEN Left(cl.pn_id,2)
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,2) = 'IY' THEN 'IN'
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'OTHER' AND ccf.cmpny_st_cd NOT IN ('','NA','UNK') THEN ccf.cmpny_st_cd 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'BCC' THEN 'CA' 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'CO/NV' THEN 'CO/NV' 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'UNICARE' THEN 'UNICARE' 
  WHEN cl.clm_sor_cd = '808' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2)          
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '560' THEN 'CT' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '600' THEN 'GA' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '630' THEN 'IN' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '660' THEN 'KY' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '680' THEN 'ME' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '741' THEN 'MO' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '770' THEN 'NH' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '834' THEN 'OH' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '950' THEN 'WI' 
  WHEN cl.clm_sor_cd = '823' AND clp.prov_st_cd IN ('IN','KY','OH', 'MO','WI') THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '823' AND mbu.brnd_desc = 'UNICARE' THEN 'UNICARE' 
  WHEN cl.clm_sor_cd = '823' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2) 
  WHEN cl.clm_sor_cd = '809' THEN 'NY'  
  WHEN cl.clm_sor_cd = '869' THEN 'VA'  
  WHEN cl.clm_sor_cd = '896' AND mbu.brnd_desc = 'BCC' THEN 'CA' 
  WHEN cl.clm_sor_cd = '896' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2) 
  WHEN cl.clm_sor_cd = '1104' AND clp.prov_st_cd IS NOT NULL THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '1104' AND mbu.brnd_desc = 'BCC' THEN 'CA' 
  WHEN cl.clm_sor_cd = '1104' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2) 
  WHEN cl.clm_sor_cd = '1104' AND mbu.brnd_desc = 'Blue LA' THEN 'LA' 
  WHEN cl.clm_sor_cd = '1104' AND Substr(mbu.mbu_cf_cd,1,2) = 'AG' THEN Substr(mbu.mbu_cf_cd,3,2) 
  ELSE 'OTH' END AS SERV_PROV_STATE

这种写法把重复的host条件只判断一次，内层CASE专注处理不同的SCCF前缀和附加条件，代码结构更清晰，也减少了重复代码量。

二、映射表方案（适合长期维护） #

如果后续SCCF前缀和州的对应规则可能变动，或者规则会越来越多，用映射表+JOIN的方式比CASE更易维护：

1. 先创建映射表，存储SCCF前缀与州的对应关系：

CREATE TABLE sccf_state_map (
    sccf_prefix VARCHAR(3) NOT NULL,
    state_cd VARCHAR(2) NOT NULL,
    prcsg_unit_id VARCHAR(2) NULL -- 针对需要处理prcsg_unit_id的情况，允许为空
);
-- 插入现有规则数据
INSERT INTO sccf_state_map (sccf_prefix, state_cd, prcsg_unit_id)
VALUES
('040','CA',NULL),('540','CA',NULL),('041','CA',NULL),('044','CA',NULL),('046','CA',NULL),
('051','CO',NULL),('551','CO',NULL),('050','CO',NULL),('550','CO',NULL),('052','CO',NULL),
('266','NV',NULL),('766','NV',NULL),('265','NV',NULL),('765','NV',NULL),('267','NV',NULL),
('131','IN',NULL),('631','IN',NULL),('132','IN',NULL),('130','IN',NULL),('630','IN',NULL),
('161','KY',NULL),('661','KY',NULL),('162','KY',NULL),('160','KY',NULL),('660','KY',NULL),
('748','MO',NULL),('749','MO',NULL),('241','MO',NULL),('741','MO',NULL),('248','MO',NULL),
('330','OH',NULL),('830','OH',NULL),('329','OH',NULL),('332','OH',NULL),('834','OH',NULL),
('458','WI',NULL),('454','WI',NULL),('954','WI',NULL),('450','WI',NULL),('950','WI',NULL),
('425','VA',NULL),('925','VA',NULL),('427','VA',NULL),('429','VA',NULL),
('254','NY',NULL),('246','NY',NULL),('746','NY',NULL),('308','NY',NULL),
('102','GA','8Y'),('602','GA','8Y'),('101','GA','8Y'),('601','GA','8Y'),('103','GA','8Y'),
('062','CT','7W'),('562','CT','7W'),('063','CT','8M'),('563','CT','8M'),('064','CT','8M'),
('182','ME','7Y'),('682','ME','7Y'),('183','ME','7Y'),('685','ME','7Y'),('185','ME','7Y'),
('271','NH','8D'),('771','NH','8D'),('272','NH','8D'),('769','NH','8D'),('269','NH','8D');

2. 在主查询中通过LEFT JOIN关联映射表，替代原来的多个CASE分支：

, CASE
  WHEN c.clm_its_host_cd IN ('HOST','JAACL') THEN
    CASE
      WHEN c.clm_sor_cd <> '808' THEN ''
      -- 用JOIN后的映射结果替代多个WHEN条件
      WHEN sm.state_cd IS NOT NULL AND (sm.prcsg_unit_id IS NULL OR ca.prcsg_unit_id = sm.prcsg_unit_id) THEN sm.state_cd
    END
  -- 以下保留原有的其他分支逻辑，与原代码一致
  WHEN cl.clm_sor_cd = '808' AND clp.prov_st_cd IN ('CA', 'CO', 'NV', 'IN', 'KY', 'OH', 'MO', 'WI', 'NH', 'ME', 'CT', 'NY', 'VA', 'GA') THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,4) = 'SPEC' THEN 'GA' 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,4) = 'HOSP' THEN 'GA' 
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,2) IN ('CT','ME','NY','VA','NH','MO','OH','KY','WI') THEN Left(cl.pn_id,2)
  WHEN cl.clm_sor_cd = '808' AND Left(cl.pn_id,2) = 'IY' THEN 'IN'
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'OTHER' AND ccf.cmpny_st_cd NOT IN ('','NA','UNK') THEN ccf.cmpny_st_cd 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'BCC' THEN 'CA' 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'CO/NV' THEN 'CO/NV' 
  WHEN cl.clm_sor_cd = '808' AND mbu.brnd_desc = 'UNICARE' THEN 'UNICARE' 
  WHEN cl.clm_sor_cd = '808' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2)          
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '560' THEN 'CT' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '600' THEN 'GA' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '630' THEN 'IN' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '660' THEN 'KY' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '680' THEN 'ME' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '741' THEN 'MO' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '770' THEN 'NH' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '834' THEN 'OH' 
  WHEN cl.clm_sor_cd = '824' AND c.srvcg_plan_cd = '950' THEN 'WI' 
  WHEN cl.clm_sor_cd = '823' AND clp.prov_st_cd IN ('IN','KY','OH', 'MO','WI') THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '823' AND mbu.brnd_desc = 'UNICARE' THEN 'UNICARE' 
  WHEN cl.clm_sor_cd = '823' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2) 
  WHEN cl.clm_sor_cd = '809' THEN 'NY'  
  WHEN cl.clm_sor_cd = '869' THEN 'VA'  
  WHEN cl.clm_sor_cd = '896' AND mbu.brnd_desc = 'BCC' THEN 'CA' 
  WHEN cl.clm_sor_cd = '896' AND Substr(mbu.brnd_desc,1, 4)='BCBS' THEN Substr(mbu.brnd_desc,5,2) 
  WHEN cl.clm_sor_cd = '1104' AND clp.prov_st_cd IS NOT NULL THEN clp.prov_st_cd 
  WHEN cl.clm_sor_cd = '1104' AND mbu.brnd_desc = 'BCC' THEN '