SQL 分隔数据转换为多值IN列表|极客教程

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