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将值拆分为多行
您可以使用递归操作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
参见演示。
结果:
关口 |
---|
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句