SQLite 将单行转换为多行

Question

SQLite
我想将由 ',' 分隔的单行值转换为多行
例子 :
单行

6,7,8,9,10,11,12,13,14,15,16

结果必须是:
多行

6
7
8
9
10
12
13
14
15
16

我尝试用 substr 函数来做，但得到了意想不到的结果

select 
 numbers.n,
substr(CbahiHSSpecialtyUnits.units,numbers.n,1) 
from
  numbers inner join CbahiHSSpecialtyUnits
  on  LENGTH(CbahiHSSpecialtyUnits.units)
     - LENGTH(REPLACE(CbahiHSSpecialtyUnits.units, ',', ''))>=numbers.n-1
  WHERE HsSubStandardID=22 and SpecialtyID=2 and  numbers.n>0
  order by numbers.n;

一件好事是我得到的行数是正确的..但是应该分开的值是错误的..
请注意数字表是我在这篇文章的帮助下为索引目的而创建的。
SQL split values to multiple rows

Answer 1

你可以用递归 CTE 来做到这一点:

WITH cte AS (
  SELECT SUBSTR(Units, 1, INSTR(Units || ',', ',') - 1) col,
         SUBSTR(Units, INSTR(Units || ',', ',') + 1) value
  FROM CbahiHSSpecialtyUnits
  WHERE HsSubStandardID=22 AND SpecialtyID = 2
  UNION ALL
  SELECT SUBSTR(value, 1, INSTR(value || ',', ',') - 1),
         SUBSTR(value, INSTR(value || ',', ',') + 1)
  FROM cte
  WHERE LENGTH(value) > 0
)
SELECT col 
FROM cte
WHERE col + 0 > 0

或者，如果您知道数字的上限是，请说 20并且数字之间没有重复:

WITH cte AS (SELECT 1 col UNION ALL SELECT col + 1 FROM cte WHERE col < 20)
SELECT c.col 
FROM cte c INNER JOIN CbahiHSSpecialtyUnits u
ON ',' || u.Units || ',' LIKE '%,' || c.col || ',%'
WHERE HsSubStandardID=22 AND SpecialtyID = 2

见 demo .
结果:


上校


6

7

8

9

10

11

12

13

14

15

16