分割逗号分隔的值并将其映射到SQLite中的原始ID

宝贝对不起

我有一个表articles，其中包含以下格式的数据：
id|categories
' C; P) R" Z0 h! K2 N% Y4 u--+----------
1|123,13,43
4 h" O$ M# P( Y; T. J' [8 O4 Z2|1,3,15
3|9,17,44,18,3
) o) P  D$ E: K出于测试目的，您可以使用以下SQL命令创建该表：
$ a" _3 P1 Z. \CREATE TABLE articles(id INTEGER PRIMARY KEY, categories TEXT);
1 y5 S7 c  A' Y+ vINSERT INTO articles VALUES(1, '123,13,43'), (2, '1,3,15'), (3, '9,17,44,18,3');
' y- X: [4 w, N! C3 v  \' n; ?现在，我想拆分categories列的值，以便结果如下表所示：
id|category
& g# ?. h% x! D# l" k--+--------
6 y; L' u5 ?& n9 u4 B( A8 w: \8 ~* J1|123
: @, a1 D. y6 `7 r% u: F- p1|13
1|43
2|1
2|3
( }' q( z9 T' Q1 \- N2|15
$ u( M8 f# R+ o4 i3 L, R  y3|9
3|17
3|44
3|18
7 L4 W' ~) L  H$ v( _% V3|3
3 m; J9 v  F' H" [1 t% M- A* B6 M' B如您所见，我想将原始表转换为First普通形式。
我已经知道如何分割只是 一个
. e; B* r* {$ v3 p这样行，从这个答案。下面的代码示例仅获取第二行（即id =2的那一行），并以所需的方式拆分它们：
WITH split(article_id, word, str, offsep) AS
) S- I) z& m' Q# e/ `3 n, X(
& Q* r6 S0 l% A$ J6 e9 O. k* p' D    VALUES
    (
        2,
        '',
        (SELECT categories FROM articles WHERE id=2),
5 Q3 w# ^7 D) A8 G  B3 b2 ?        1
    )
    UNION ALL
9 N8 _9 T* l7 Y# K    SELECT
        article_id,
" }, z! ]7 M. b. L# L# Z" @% o        substr(str, 0, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END),
        ltrim(substr(str, instr(str, ',')), ','),
  c- n* u6 q: F& M        instr(str, ',')
# R) }8 ?+ M' T3 J7 [$ |4 I" _        FROM split
        WHERE offsep
) SELECT article_id, word FROM split WHERE word!='';
# m$ s% d! J4 M当然，这是非常不灵活的，因为商品ID需要进行硬编码。因此，现在我的问题是：我必须添加或更改上面的SQLite代码以使其对所有行进行操作并输出所需的结果吗？
               
解决方案:
               
/ {. H# R& a0 n0 ^" H3 ]

                经过一番摸索，我终于自己弄清楚了解决方案。它还照顾具有''或NULL作为值的行categories：
-- create temporary table which buffers the maximum article ID, because SELECT MAX can take a very long time on huge databases
# r2 [& W- P3 f) p& wDROP TABLE IF EXISTS max_article_id;
2 ]1 c' N2 \/ a3 i4 eCREATE TEMP TABLE max_article_id(num INTEGER);
INSERT INTO max_article_id VALUES((SELECT MAX(id) FROM articles));
WITH RECURSIVE split(article_id, word, str, offsep) AS
9 @1 E' q; i6 s, }3 d9 Q  B) @( }(
    VALUES ( 0, '', '', 0 )                                      -- begin with dummy article 0 (which does not actually exist) to avoid code duplication
    UNION ALL
% q) E  o  M& A, o" Y( F  e- w! C    SELECT
        CASE WHEN offsep==0 OR str IS NULL
            THEN article_id+1                                    -- go to next article if the current one is finished
            ELSE article_id                                      -- and keep the current one in the opposite case
        END,
0 P- r2 t4 F* e; e7 z. v        CASE WHEN offsep==0 OR str IS NULL
) x* j: c1 Q3 e4 Q0 Q            THEN ''
            ELSE substr(str, 0, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END)
        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- when str==NULL, then there has been a NULL value for the categories cell of the current article
            THEN (SELECT categories FROM articles WHERE id=article_id+1)
& b' V% X) N: e7 A            ELSE ltrim(substr(str, instr(str, ',')), ',')
8 V/ B7 h$ p  S. l& q- Q: T, S        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- offsep==0 means that the splitting was finished in the previous iteration
            THEN 1                                               -- offsep==1 means that splitting the categories for a new article will begin in the next iteration
            ELSE instr(str, ',')                                 -- the actual string splitting stuff is explained and taken from here: http://stackoverflow.com/a/32051164
* J  }- h- }# I! W, M        END
. D0 }0 Z/ O9 w1 \6 `$ R        FROM split
        WHERE article_id<=(SELECT * FROM max_article_id)         -- stop getting new articles when the maximum article ID is reached
) SELECT article_id, word AS category FROM split WHERE word!=''; -- only select article_id and word from the result to use output the desired table layout