使用sqlite在零件中拆分值 [英] Split values in parts with sqlite
本文介绍了使用sqlite在零件中拆分值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在努力转换
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格式的数据:
<
创建表数据(
一个 TEXT,
许多 TEXT
);
插入数据值('a','a1,a2,a3');
插入数据值( b, b1,b3);
插入数据值( c, c2,c1);
解决方案可能是递归的公共表表达式。
以下是一个类似于单行的示例:
使用递归列表(元素,余数)AS(
SELECT NULL AS元素'1, 2,3,4,5'AS余数
UNION ALL
SELECT
CASE
当INSTR(余数,',')> 0则
SUBSTR(余数,0,INSTR(余数,','))
ELSE
余数
END作为元素,
情况
当INSTR(余数,',')> 0 THEN
SUBSTR(余数,INSTR(余数,',')+1)
ELSE
NULL
从列表
处结束余数
是不是空
)
SELECT * FROM list;
(最初来自此博客文章: https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common -table-expression-tutorial )
它产生:
元素|其余
-------------------
NULL | 1,2,3,4,5
1 | 2,3,4,5
2 | 3,4,5
3 | 4,5
4 | 5
5 | NULL
因此,问题在于将其应用于表中的每一行。
解决方案
是的,解决方案是使用递归公用表表达式:
,其中x(one,firstone,rest)为
(选择一个,substr(many,1,instr(many,',')-1)作为firstone,substr(many,instr(many, ',')+ 1)作为其他数据的休止符,例如%,%
UNION ALL
选择一个,substr(rest,1,instr(rest,',')-1)作为firstone,substr(rest,instr(rest,',')+ 1)从x处作为休息处,如%,% LIMIT 200
)
从x UNION ALL中选择一个,firstone ,从x处休息,而不是像%,%
那样按1休息;
输出:
a | a1
a | a2
a | a3
b | b1
b | b3
c | c2
c | c1
I'm struggling to convert
a | a1,a2,a3
b | b1,b3
c | c2,c1
to:
a | a1
a | a2
a | a3
b | b1
b | b2
c | c2
c | c1
Here are data in sql format:
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');
The solution is probably recursive Common Table Expression.
Here's an example which does something similar to a single row:
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;
(originally from this blog post: https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial)
It produces:
element | remainder
-------------------
NULL | 1,2,3,4,5
1 | 2,3,4,5
2 | 3,4,5
3 | 4,5
4 | 5
5 | NULL
the problem is thus to apply this to each row in a table.
解决方案
Yes, a recursive common table expression is the solution:
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;
Output:
a|a1
a|a2
a|a3
b|b1
b|b3
c|c2
c|c1
这篇关于使用sqlite在零件中拆分值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文