6.11 将分隔数据转换为多值IN列表
问题
已经有了分隔数据,想要将其转换为WHERE子句IN列表中的项目。考虑下面的字符串:
7654,7698,7782,7788
要将该字符串用在WHERE子句中,但是下面的SQL语句是错误的,因为EMPNO是一个数值列:
select ename,sal,deptno
from emp
where empno in ( ‘7654,7698,7782,7788‘ )
因为EMPNO是一个数值列,而此IN列表是一个字符串值,所以此SQL语句会失败。现要将此字符串转换为用逗号分解的数值列表。
解决方案
表面上看SQL应该将分隔字符串作为一个分隔值列表对待,但是实际情况不是这样。当SQL遇到括在引号中的逗号时,并不知道此符号表示多值列表,SQL必须将括在引号中的内容作为一个整体对待,也就是一个字符串值。因此必须将字符串分解为各个单独的EMPNO。这种解决方案的关键就是需要遍历字符串,但并不是一个字符一个字符地遍历,而是要将这个字符串转换为有效的EMPNO值。
DB2
通过遍历传递给IN列表的字符串,可以很轻松地将其转换为若干行。在这里函数ROW_NOMBER、LOCATE和SUBSTR尤其有用:
1 select empno,ename,sal,deptno
2 from emp
3 where empno in (
4 select cast(substr(c,2,locate(‘,‘,c,2)-2) as integer) empno
5 from (
6 select substr(csv.emps,cast(iter.pos as integer)) as c
7 from (select ‘,‘||‘7654,7698,7782,7788‘||‘,‘ emps
8 from t1) csv,
9 (select id as pos
10 from t100 ) iter
11 where iter.pos <= length(csv.emps)
12 ) x
13 where length(c) > 1
14 and substr(c,1,1) = ‘,‘
15 ) y
MySQL
通过遍历传递给IN列别的字符串,可以很轻松地将其转换为若干行:
1 select empno, ename, sal, deptno
2 from emp
3 where empno in
4 (
5 select substring_index(
6 substring_i