用SQL查询从表的新列中提取值

Question

我在数据库中有一个文本列的表，其中每行的记录记录为列表，例如列test:

[['a','1'],['b','2'],['c','3']]
[['a','2'],['a','4'],['c','8'],['d','3']
[['b','3'],['c',5'],['c','6'],['d','4']]

我想将值提取到三个新列x y z中，其中

• x包含a和b的所有值;
• y包含c的所有值;
• z包含d的所有值，例如

:

x                         y                    z
['a','1'],['b','2']    ['c','3']
['a','2'],['a','4']    ['c','8']                ['d','3']
['b','3']              ['c','5'],['c','6']      ['d','4']

有可能用SQL语句做到这一点吗?

Thanks in advance

Answer 1

您需要一个递归CTE来分割每一行，然后根据您的需求GROUP_CONCAT()来收集值:

WITH cte AS (
SELECT rowid,
SUBSTR(test, 1, INSTR(test || ';', ';') - 1) col,
SUBSTR(test, INSTR(test || ';', ';') + 1) rest
FROM (SELECT rowid, REPLACE(REPLACE(REPLACE(test, '[[', '['), ']]', ']'), '],[', '];[') test FROM tablename)
UNION ALL
SELECT rowid,
SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
SUBSTR(rest, INSTR(rest || ';', ';') + 1)
FROM cte
WHERE LENGTH(rest) > 0
)
SELECT GROUP_CONCAT(CASE WHEN col LIKE '[''a'',%' OR col LIKE '[''b'',%' THEN col END) x,
GROUP_CONCAT(CASE WHEN col LIKE '[''c'',%' THEN col END) y,
GROUP_CONCAT(CASE WHEN col LIKE '[''d'',%' THEN col END) z
FROM cte
GROUP BY rowid

Results:

[' a ', ' 1 '], [' b ', ' 2 ']['c'，'3'][' a ', ' 2 '], [' a ', ' 4 ']['c'，'8'][' d ', ' 3 ']['b'，'3'][' c ', 5 '], [' c ', ' 6 '][' d ', ' 4 ']