SQLite中如何批量拆分并关联匹配Hobbies与SubHobbies字段

要实现将Clients表中的Hobbies和SubHobbies字段拆分，同时保证子爱好与对应爱好匹配，核心思路是通过递归CTE拆分字段并为每个元素添加位置索引，再根据索引关联实现匹配。以下是针对你给出的示例场景的解决方案：

解决方案代码 #

WITH SplitHobbies AS (
    -- 递归拆分Hobbies字段，生成带位置索引的爱好行
    SELECT 
        ID,
        Name,
        TRIM(SUBSTR(Hobbies, 1, INSTR(Hobbies || ',', ',') - 1)) AS Hobby,
        TRIM(SUBSTR(Hobbies, INSTR(Hobbies || ',', ',') + 1)) AS RemainingHobbies,
        1 AS HobbyIndex
    FROM Clients
    WHERE Hobbies IS NOT NULL AND Hobbies != ''
    UNION ALL
    SELECT 
        ID,
        Name,
        TRIM(SUBSTR(RemainingHobbies, 1, INSTR(RemainingHobbies || ',', ',') - 1)) AS Hobby,
        TRIM(SUBSTR(RemainingHobbies, INSTR(RemainingHobbies || ',', ',') + 1)) AS RemainingHobbies,
        HobbyIndex + 1
    FROM SplitHobbies
    WHERE RemainingHobbies IS NOT NULL AND RemainingHobbies != ''
),
SplitSubHobbies AS (
    -- 递归拆分SubHobbies字段，生成带位置索引的子爱好行
    SELECT 
        ID,
        TRIM(SUBSTR(SubHobbies, 1, INSTR(SubHobbies || ',', ',') - 1)) AS SubHobby,
        TRIM(SUBSTR(SubHobbies, INSTR(SubHobbies || ',', ',') + 1)) AS RemainingSubHobbies,
        1 AS SubHobbyIndex
    FROM Clients
    WHERE SubHobbies IS NOT NULL AND SubHobbies != ''
    UNION ALL
    SELECT 
        ID,
        TRIM(SUBSTR(RemainingSubHobbies, 1, INSTR(RemainingSubHobbies || ',', ',') - 1)) AS SubHobby,
        TRIM(SUBSTR(RemainingSubHobbies, INSTR(RemainingSubHobbies || ',', ',') + 1)) AS RemainingSubHobbies,
        SubHobbyIndex + 1
    FROM SplitSubHobbies
    WHERE RemainingSubHobbies IS NOT NULL AND RemainingSubHobbies != ''
),
HobbyCounts AS (
    -- 统计每个用户的爱好总数，用于反向索引匹配
    SELECT ID, COUNT(*) AS TotalHobbies
    FROM SplitHobbies
    GROUP BY ID
)
-- 通过ID和反向索引关联，实现示例中的爱好-子爱好匹配
SELECT 
    sh.ID,
    sh.Name,
    sh.Hobby AS Hobbies,
    ssh.SubHobby AS SubHobbies
FROM SplitHobbies sh
JOIN HobbyCounts hc ON sh.ID = hc.ID
JOIN SplitSubHobbies ssh ON sh.ID = ssh.ID AND ssh.SubHobbyIndex = hc.TotalHobbies - sh.HobbyIndex + 1
ORDER BY sh.ID, sh.HobbyIndex;

代码说明 #

1. SplitHobbies CTE：递归拆分Hobbies字符串，为每个爱好分配一个递增的HobbyIndex，标记其在原列表中的位置。
2. SplitSubHobbies CTE：用相同逻辑拆分SubHobbies字符串，生成SubHobbyIndex标记子爱好的位置。
3. HobbyCounts CTE：统计每个用户的爱好总数，用于实现示例中的反向匹配（如第1个爱好对应最后1个子爱好）。
4. 最终关联查询：通过ID和计算后的索引关联两个拆分结果，得到爱好与子爱好一一对应的目标数据。

适配调整 #

如果你的实际数据中，爱好与子爱好是正向顺序对应（第1个爱好对应第1个子爱好），可简化关联逻辑，去掉HobbyCounts CTE，将最后的关联条件改为：

JOIN SplitSubHobbies ssh ON sh.ID = ssh.ID AND sh.HobbyIndex = ssh.SubHobbyIndex

内容的提问来源于stack exchange，提问作者elixir