SQL两表字段子串匹配取公共值 IN子查询返回多值报错如何解决

原代码问题排查 #

• FROM子句同时引入两张表未加关联条件，会生成笛卡尔积，结果冗余且查询效率极低
• 子查询未过滤testmethodid不含|的记录，INSTR匹配不到分隔符时返回0，会导致SUBSTR计算参数异常，部分数据库会将该类异常转化为子查询返回值异常的报错提示
• 部分数据库SUBSTR起始位从1计数，写0虽然兼容部分场景，但通用性不足

推荐写法：JOIN关联匹配（性能更稳定，逻辑更清晰） #

直接将子串截取逻辑放在关联条件中完成匹配：

SELECT DISTINCT sd.sourceworkitemid 
FROM sdidata sd
INNER JOIN u_prodstypetest pst 
  ON sd.sourceworkitemid = SUBSTR(pst.testmethodid, 1, INSTR(pst.testmethodid, '|', 1) - 1)
WHERE sd.keyid1 = 'S-20210719-00000003'
-- 过滤不含分隔符的无效记录，避免计算错误
AND INSTR(pst.testmethodid, '|', 1) > 0;

可选写法：修正IN子查询版本 #

如果需要保留IN子查询的写法，先过滤无效记录即可：

SELECT DISTINCT sd.sourceworkitemid 
FROM sdidata sd 
WHERE sd.keyid1 = 'S-20210719-00000003' 
AND sd.sourceworkitemid IN (
  SELECT SUBSTR(testmethodid, 1, INSTR(testmethodid,'|',1)-1) 
  FROM u_prodstypetest
  WHERE INSTR(testmethodid,'|',1) > 0
);

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