CASE语句运行报错:已达到表达式服务限制问题咨询
问题解决方案
你遇到的报错是因为单个CASE语句中嵌套的IN判断包含的枚举值过多,触发了数据库的表达式复杂度限制,以下是可落地的解决方法,按推荐优先级排序:
方案1:新建邮编-区域映射表(最推荐,长期可维护)
这种方法彻底避免了硬编码冗长的CASE逻辑,后续调整区域匹配规则仅需修改映射表数据,无需调整查询代码:
- 新建映射表
CREATE TABLE PostcodeRegionMap ( PostCodeABR VARCHAR(10) PRIMARY KEY, Region VARCHAR(50) NOT NULL );
- 插入所有邮编前缀和对应区域的映射关系,示例插入语句:
INSERT INTO PostcodeRegionMap (PostCodeABR, Region) VALUES ('DG','Scotland'),('KW','Scotland'),('IV','Scotland'),('PH','Scotland'),('AB','Scotland'), ('DD','Scotland'),('PA','Scotland'),('FK','Scotland'),('KY','Scotland'),('G','Scotland'), ('EH','Scotland'),('ML','Scotland'),('KA','Scotland'),('TD','Scotland'), ('BT','Ireland'), -- 按原有CASE逻辑补全所有邮编前缀对应的区域即可 ('WD','London'),('EN','London'),('HA','London') -- 后续补全所有London对应的前缀
- 改写查询语句,用LEFT JOIN关联映射表即可:
SELECT T0.CardCode, T0.CardName, T0.PostCode, ISNULL(T1.Region, 'No Region') AS Region FROM [dbo].[REPS-PostcodeABBR] T0 LEFT JOIN PostcodeRegionMap T1 ON T0.PostCodeABR = T1.PostCodeABR
方案2:查询内构造临时映射(无需建表,适合无表创建权限的场景)
如果没有权限新建实体表,可以直接在查询中用VALUES构造临时映射集,效果和方案1一致:
SELECT T0.CardCode, T0.CardName, T0.PostCode, ISNULL(T1.Region, 'No Region') AS Region FROM [dbo].[REPS-PostcodeABBR] T0 LEFT JOIN ( VALUES ('DG','Scotland'),('KW','Scotland'),('IV','Scotland'),('PH','Scotland'),('AB','Scotland'), ('DD','Scotland'),('PA','Scotland'),('FK','Scotland'),('KY','Scotland'),('G','Scotland'), ('EH','Scotland'),('ML','Scotland'),('KA','Scotland'),('TD','Scotland'), ('BT','Ireland'), -- 按原有CASE逻辑补全所有邮编前缀对应的区域即可 ('WD','London'),('EN','London'),('HA','London') ) AS T1(PostCodeABR, Region) ON T0.PostCodeABR = T1.PostCodeABR
方案3:修正UNION ALL逻辑(仅作参考,不推荐长期使用)
你之前用UNION ALL出错是因为两个子查询都保留了ELSE逻辑,导致互相覆盖,修正方法为先查所有匹配到区域的记录,再单独查无匹配的记录:
-- 第一部分:前半部分区域匹配的记录 SELECT T0.CardCode, T0.CardName, T0.PostCode, CASE WHEN T0.PostCodeABR IN ('DG','KW','IV','PH','AB','DD','PA','FK','KY','G','EH','ML','KA','TD') THEN 'Scotland' WHEN T0.PostCodeABR IN ('BT') THEN 'Ireland' WHEN T0.PostCodeABR IN ('CA','NE','DH','SR','TS','DL','LA','BD','HG','YO','HX','LS','FY','PR','BB','L','WN','BL','OL') THEN 'North M62' WHEN T0.PostCodeABR IN ('CH','WA','CW','SK','M','HD','WF','DN','HU','DE','NG','LN','S') THEN 'South M62' WHEN T0.PostCodeABR IN ('LL','SY','LD','SA','CF','NP') THEN 'Wales' END AS Region FROM [dbo].[REPS-PostcodeABBR] T0 WHERE T0.PostCodeABR IN ('DG','KW','IV','PH','AB','DD','PA','FK','KY','G','EH','ML','KA','TD','BT','CA','NE','DH','SR','TS','DL','LA','BD','HG','YO','HX','LS','FY','PR','BB','L','WN','BL','OL','CH','WA','CW','SK','M','HD','WF','DN','HU','DE','NG','LN','S','LL','SY','LD','SA','CF','NP') UNION ALL -- 第二部分:后半部分区域匹配的记录 SELECT T0.CardCode, T0.CardName, T0.PostCode, CASE WHEN T0.PostCodeABR IN ('NR','IP','CB') THEN 'East Anglia' WHEN T0.PostCodeABR IN ('SN','BS','BA','SP','BH','DT','TA','EX','TQ','PL','TR') THEN 'South West' WHEN T0.PostCodeABR IN ('LU','AL','HP','SG','SL','RG','SO','GU','PO','BN','RH','TN','ME','CT','SS','CM','CO') THEN 'South East' WHEN T0.PostCodeABR IN ('ST','TF','WV','WS','DY','B','WR','HR','GL','OX','CV','NN','MK','PE','LE') THEN 'Midlands' WHEN T0.PostCodeABR IN ('WD','EN','HA','N','NW','UB','W','WC','EC','E','IG','RM','DA','BR','CR','SM','KT','TW','SW') THEN 'London' END AS Region FROM [dbo].[REPS-PostcodeABBR] T0 WHERE T0.PostCodeABR IN ('NR','IP','CB','SN','BS','BA','SP','BH','DT','TA','EX','TQ','PL','TR','LU','AL','HP','SG','SL','RG','SO','GU','PO','BN','RH','TN','ME','CT','SS','CM','CO','ST','TF','WV','WS','DY','B','WR','HR','GL','OX','CV','NN','MK','PE','LE','WD','EN','HA','N','NW','UB','W','WC','EC','E','IG','RM','DA','BR','CR','SM','KT','TW','SW') UNION ALL -- 第三部分:完全无匹配的记录,统一标为No Region SELECT T0.CardCode, T0.CardName, T0.PostCode, 'No Region' AS Region FROM [dbo].[REPS-PostcodeABBR] T0 WHERE T0.PostCodeABR NOT IN ( 'DG','KW','IV','PH','AB','DD','PA','FK','KY','G','EH','ML','KA','TD','BT','CA','NE','DH','SR','TS','DL','LA','BD','HG','YO','HX','LS','FY','PR','BB','L','WN','BL','OL','CH','WA','CW','SK','M','HD','WF','DN','HU','DE','NG','LN','S','LL','SY','LD','SA','CF','NP', 'NR','IP','CB','SN','BS','BA','SP','BH','DT','TA','EX','TQ','PL','TR','LU','AL','HP','SG','SL','RG','SO','GU','PO','BN','RH','TN','ME','CT','SS','CM','CO','ST','TF','WV','WS','DY','B','WR','HR','GL','OX','CV','NN','MK','PE','LE','WD','EN','HA','N','NW','UB','W','WC','EC','E','IG','RM','DA','BR','CR','SM','KT','TW','SW' )
内容的提问来源于stack exchange,提问作者Andrew Gray
相关产品推荐
相关产品推荐

