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_NUMBERLOCATESUBSTR 非常有用。

 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 列表的字符串,我们很容易将其转换为行数据。在这里,ROWNUMSUBSTRINSTR 非常有用。

 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_NUMBERCHARINDEXSUBSTRING 非常有用。

 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 列表中的值。这些值都以逗号开头,但是要排除掉最后一行,因为最后一行只有一个孤零零的逗号。调用函数 SUBSTRSUBSTRING 筛选出以逗号开头的行,然后在那一行里找到下一个逗号,并留下两个逗号之间的所有字符。这一步完成后,接着要把找到的字符串转换为数字,这样就可以针对数值类型的 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 次出现时的位置。由于字符串前后都有逗号,就不再需要做特别的检查来确定字符串的起止位置。被传递到函数 SUBSTRINSTR(第 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 列)转换成数字,并将其放入子查询中。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程