时间戳

首页 > 解决方案 > SQLite：新表和列的逗号分隔值

database - SQLite：新表和列的逗号分隔值

问题描述

我有一个 SQLite 表：

fileId | path
1        /video/gopro/father/mov001.mp4
2        /pictures/family/father/Oldman.jpg
3        /documents/legal/father/estate/will.doc

我想将此目录结构分离到一个带有新列的新表中。新表：

fileId | path0   | path1 | path 2 | path3      | path4  .... (Define an upper limit of sub-dirs) 
1        video     gopro   father   mov001.mp4
2        pictures  family  father   Oldman.jpg
3        documents legal   father   estate       will.doc

最好的方法是什么？首先，我用字符“/”分隔字符串，但目前这只会生成新行，而不是列：

WITH split(Path, str) AS (
    SELECT '', Path||'/' FROM listfile
    UNION ALL SELECT
    substr(str, 0, instr(str, '/')),
    substr(str, instr(str, '/')+1)
    FROM split WHERE str !=''
) SELECT Path FROM split WHERE Path!='' ;

标签： databasesqlitecsvsplitdirectory

解决方案

首先path根据需要创建包含尽可能多列的新表：

CREATE TABLE tablename(
  fileId INTEGER PRIMARY KEY, 
  path0 TEXT, 
  path1 TEXT, 
  path2 TEXT, 
  path3 TEXT, 
  path4 TEXT
);

然后使用条件聚合向表中插入行：

WITH split AS (
  SELECT 0 idx,
         fileId,
         SUBSTR(SUBSTR(Path, 2), 1, INSTR(SUBSTR(Path, 2) || '/', '/') - 1) item,
         SUBSTR(SUBSTR(Path, 2), INSTR(SUBSTR(Path, 2) || '/', '/') + 1) value
  FROM listfile
  UNION ALL
  SELECT idx + 1,
         fileId,
         SUBSTR(value, 1, INSTR(value || '/', '/') - 1),
         SUBSTR(value, INSTR(value || '/', '/') + 1)
  FROM split
  WHERE LENGTH(value) > 0
)
INSERT INTO tablename 
SELECT fileId,
       MAX(CASE WHEN idx = 0 THEN item END),
       MAX(CASE WHEN idx = 1 THEN item END),
       MAX(CASE WHEN idx = 2 THEN item END),
       MAX(CASE WHEN idx = 3 THEN item END),
       MAX(CASE WHEN idx = 4 THEN item END)
FROM split
GROUP BY fileId

我修改了splitcte 以包含idx用于列顺序的path列。

见演示。
结果：

|fileId | path0     | path1  | path2  | path3      | path4   
|-----: | :-------- | :----- | :----- | :--------- | :-------
|     1 | video     | gopro  | father | mov001.mp4 | null    
|     2 | pictures  | family | father | Oldman.jpg | null    
|     3 | documents | legal  | father | estate     | will.doc

推荐阅读