作者：mobiledu2502859073 | 来源：互联网 | 2023-01-16 10:47
我正在努力转换
a | a1,a2,a3
b | b1,b3
c | c2,c1
至：
a | a1
a | a2
a | a3
b | b1
b | b2
c | c2
c | c1
以下是sql格式的数据：
CREATE TABLE data(
"one" TEXT,
"many" TEXT
);
INSERT INTO "data" VALUES('a','a1,a2,a3');
INSERT INTO "data" VALUES('b','b1,b3');
INSERT INTO "data" VALUES('c','c2,c1');
解决方案可能是递归的公共表表达式。
这是一个与单行相似的示例：
WITH RECURSIVE list( element, remainder ) AS (
SELECT NULL AS element, '1,2,3,4,5' AS remainder
UNION ALL
SELECT
CASE
WHEN INSTR( remainder, ',' )>0 THEN
SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
ELSE
remainder
END AS element,
CASE
WHEN INSTR( remainder, ',' )>0 THEN
SUBSTR( remainder, INSTR( remainder, ',' )+1 )
ELSE
NULL
END AS remainder
FROM list
WHERE remainder IS NOT NULL
)
SELECT * FROM list;
（最初来自此博客文章：https : //blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial）
它产生：
element | remainder
-------------------
NULL | 1,2,3,4,5
1 | 2,3,4,5
2 | 3,4,5
3 | 4,5
4 | 5
5 | NULL
因此，问题在于将其应用于表中的每一行。
1> Yunnosch..：
是的，解决方案是使用递归公用表表达式：
with x(one, firstone, rest) as
(select one, substr(many, 1, instr(many, ',')-1) as firstone, substr(many, instr(many, ',')+1) as rest from data where many like "%,%"
UNION ALL
select one, substr(rest, 1, instr(rest, ',')-1) as firstone, substr(rest, instr(rest, ',')+1) as rest from x where rest like "%,%" LIMIT 200
)
select one, firstone from x UNION ALL select one, rest from x where rest not like "%,%"
ORDER by one;
输出：
a|a1
a|a2
a|a3
b|b1
b|b3
c|c2
c|c1
京公网安备 11010802041100号