极客教程SQL 分隔数据转换为多值IN列表，你有一些分隔数据，想传递给 WHERE 子句的 IN 列表。考虑下面的字符串。
SQL 分隔数据转换为多值IN列表 问题描述
你有一些分隔数据，想传递给 WHERE 子句的 IN 列表。考虑下面的字符串。
7654,7698,7782,7788
你希望在 WHERE 子句里使用上述字符串，但是下面的 SQL 会由于 EMPNO 列是数值字段而执行失败。
select ename,sal,deptno
from emp
where empno in ( '7654,7698,7782,7788' )
上述 SQL 之所以失败是因为，EMPNO 列是数值类型，而 IN 列表里却只有一个字符串。你希望上述字符串能被当作逗号分隔的数值列表。
SQL 分隔数据转换为多值IN列表 解决方案
从表面上看，我们应该设法让 SQL 把分隔字符串当成一系列用逗号分隔好的值。然而，事实并非如此。如果一个逗号出现在引号里，SQL 无法知道它是一个多值列表。SQL 会把引号中的任何值当成单一的字符串数据。我们必须把该字符串打散，变成单个的 EMPNO。本解决方案的关键之处在于遍历字符串，但是并不需要深入到每一个字符。只需要遍历字符串中每一个有效的 EMPNO 即可。
DB2
遍历传递给 IN 列表的字符串，我们很容易将其转换为行数据。在这里，函数 ROW_NUMBER、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_index(list.vals,',',iter.pos),',',-1) empno
6 from (select id pos from t10) as iter,
7 (select '7654,7698,7782,7788' as vals
8 from t1) list
9 where iter.pos <=
10 (length(list.vals)-length(replace(list.vals,',','')))+1
11 ) x
Oracle
遍历传递给 IN 列表的字符串，我们很容易将其转换为行数据。在这里，ROWNUM、SUBSTR 和 INSTR 非常有用。
1 select empno,ename,sal,deptno
2 from emp
3 where empno in (
4 select to_number(
5 rtrim(
6 substr(emps,
7 instr(emps,',',1,iter.pos)+1,
8 instr(emps,',',1,iter.pos+1) -
9 instr(emps,',',1,iter.pos)),',')) emps
10 from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
11 (select rownum pos from emp) iter
12 where iter.pos <= ((length(csv.emps)-
13 length(replace(csv.emps,',')))/length(','))-1
14 )
PostgreSQL
遍历传递给 IN 列表的字符串，我们很容易将其转换为行数据。SPLIT_PART 函数能方便地把字符串解析成多个单独的数字。
1 select ename,sal,deptno
2 from emp
3 where empno in (
4 select cast(empno as integer) as empno
5 from (
6 select split_part(list.vals,',',iter.pos) as empno
7 from (select id as pos from t10) iter,
8 (select ','||'7654,7698,7782,7788'||',' as vals
9 from t1) list
10 where iter.pos <=
11 length(list.vals)-length(replace(list.vals,',',''))
12 ) z
13 where length(empno) > 0
14 ) x
SQL Server
遍历传递给 IN 列表的字符串，我们很容易将其转换为行数据。在这里，函数 ROW_NUMBER、CHARINDEX 和 SUBSTRING 非常有用。
1 select empno,ename,sal,deptno
2 from emp
3 where empno in (select substring(c,2,charindex(',',c,2)-2) as empno
4 from (
5 select substring(csv.emps,iter.pos,len(csv.emps)) as c
6 from (select ','+'7654,7698,7782,7788'+',' as emps
7 from t1) csv,
8 (select id as pos
9 from t100) iter
10 where iter.pos <= len(csv.emps)
11 ) x
12 where len(c) > 1
13 and substring(c,1,1) = ','
14 ) y
SQL 分隔数据转换为多值IN列表 扩展知识
这个解决方案的第一步就是遍历字符串，这也是最重要的一步。一旦你完成了这一步，那么剩下的就是解析字符串，用数据库提供的函数把字符串逐个转换为数值。
DB2 和 SQL Server
内嵌视图 X（第 6 ～ 11 行）遍历字符串。该解决方案的思路就是遍历字符串，因此每一行都比它前面的那行少一个字符。
,7654,7698,7782,7788,
7654,7698,7782,7788,
654,7698,7782,7788,
54,7698,7782,7788,
4,7698,7782,7788,
,7698,7782,7788,
7698,7782,7788,
698,7782,7788,
98,7782,7788,
8,7782,7788,
,7782,7788,
7782,7788,
782,7788,
82,7788,
2,7788,
,7788,
7788,
788,
88,
8,
,
注意，字符串前后都有逗号（分隔符），因此不需要特意检查字符串的起止位置。
下一步就是只保留我们想要放在 IN 列表中的值。这些值都以逗号开头，但是要排除掉最后一行，因为最后一行只有一个孤零零的逗号。调用函数 SUBSTR 或 SUBSTRING 筛选出以逗号开头的行，然后在那一行里找到下一个逗号，并留下两个逗号之间的所有字符。这一步完成后，接着要把找到的字符串转换为数字，这样就可以针对数值类型的 EMPNO 列（第 4 ～ 14 行）进行适当的评估。
EMPNO
------
7654
7698
7782
7788
最后，把结果集放入一个子查询里，并返回想要得到的行。
MySQL
内嵌视图（第 5 ～ 9 行）遍历字符串。第 10 行的表达式决定了字符串里包含多少个值，这是通过找出字符串中有多少个逗号（分隔符）并加上 1 来实现的。函数 SUBSTRING_INDEX（第 6 行）返回字符串中第 n 次出现逗号（分隔符）之前（从左边开始）的所有字符。
+---------------------+
| empno |
+---------------------+
| 7654 |
| 7654,7698 |
| 7654,7698,7782 |
| 7654,7698,7782,7788 |
+---------------------+
然后，上面得到的行会被再次传递给 SUBSTRING_INDEX 函数（第 5 行）。这一次的参数里，指定的第 n 次出现分隔符的参数值是 -1，这意味着从右边数第 n 次出现分隔符后，其右侧所有字符都会被保留下来。
+-------+
| empno |
+-------+
| 7654 |
| 7698 |
| 7782 |
| 7788 |
+-------+
最后，将上述结果放入一个子查询中。
Oracle
第一步是遍历字符串。
select emps,pos
from (select ','||'7654,7698,7782,7788'||',' emps
from t1) csv,
(select rownum pos from emp) iter
where iter.pos <=
((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1
EMPS POS
--------------------- ----------
,7654,7698,7782,7788, 1
,7654,7698,7782,7788, 2
,7654,7698,7782,7788, 3
,7654,7698,7782,7788, 4
上述查询返回的行数代表了列表中有多少个值。POS 列至关重要，有了它才能把字符串解析成单个的值。使用 SUBSTR 函数和 INSTR 函数解析字符串。POS 列被用来找出分隔符在每个字符串中第 n 次出现时的位置。由于字符串前后都有逗号，就不再需要做特别的检查来确定字符串的起止位置。被传递到函数 SUBSTR 和 INSTR（第 7 ～ 9 行）的值能找出分隔符第 n 次和第 n+1 次出现的位置。通过使用下一个逗号（在字符串中下一个逗号出现的位置）的返回值减去当前逗号（在字符串中当前逗号所在的位置）的返回值，我们就能从字符串中提取出每一个值。
select substr(emps,
instr(emps,',',1,iter.pos)+1,
instr(emps,',',1,iter.pos+1) -
instr(emps,',',1,iter.pos)) emps
from (select ','||'7654,7698,7782,7788'||',' emps
from t1) csv,
(select rownum pos from emp) iter
where iter.pos <=
((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1
EMPS
-----------
7654,
7698,
7782,
7788,
最后，删除每个值后面的逗号，将其转换为数字并放入子查询中。
PostgreSQL
内嵌视图 Z（第 6 ～ 9 行）遍历字符串。返回的行数取决于字符串中含有多少个值。为了找出字符串中含有多少个值，用包含分隔符的字符串总长度减去去掉了分隔符的字符串长度（第 9 行）。SPLIT_PART 函数可以解析字符串，并找到分隔符第 n 次出现之前的那个值。
select list.vals,
split_part(list.vals,',',iter.pos) as empno,
iter.pos
from (select id as pos from t10) iter,
(select ','||'7654,7698,7782,7788'||',' as vals
from t1) list
where iter.pos <=
length(list.vals)-length(replace(list.vals,',',''))
-----------------------+-------+-----
,7654,7698,7782,7788, | | 1
,7654,7698,7782,7788, | 7654 | 2
,7654,7698,7782,7788, | 7698 | 3
,7654,7698,7782,7788, | 7782 | 4
,7654,7698,7782,7788, | 7788 | 5
最后，把这些值（EMPNO 列）转换成数字，并将其放入子查询中。