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

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

经过一番摸索，我终于自己弄清楚了解决方案。它还照顾具有

''

或

NULL

作为值的行

categories

：

-- create temporary table which buffers the maximum article ID, because SELECt MAX can take a very long time on huge databasesDROP TABLE IF EXISTS max_article_id;CREATE 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(    VALUES ( 0, '', '', 0 )     -- begin with dummy article 0 (which does not actually exist) to avoid pre duplication    UNIOn ALL    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,        CASE WHEN offsep==0 OR str IS NULL 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) ELSE ltrim(substr(str, instr(str, ',')), ',')        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        END        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