SQLite中对BOOKS表Categories列每行条目按字母排序的需求

方案思路 #

由于SQLite 3.4.4不支持GROUP_CONCAT的ORDER BY子句，也没有窗口函数，我们需要通过递归CTE拆分字符串 + 自连接生成排序序列 + 递归拼接排序后类别的组合方式来实现每行内的类别首字母排序。

具体SQL实现 #

WITH RECURSIVE split_cats AS (
    -- 初始步骤：拆分每行的第一个类别，去除外层方括号和类别两端的双引号
    SELECT
        id,
        TRIM(categories, '[]') AS remaining,
        TRIM(
            SUBSTR(TRIM(categories, '[]'), 1, INSTR(TRIM(categories, '[]'), '","') - 1),
            '"'
        ) AS category
    FROM books
    WHERE categories IS NOT NULL AND categories != '[]'
    
    UNION ALL
    
    -- 递归步骤：逐行拆分剩余的类别
    SELECT
        id,
        SUBSTR(remaining, INSTR(remaining, '","') + 3),
        CASE
            WHEN INSTR(remaining, '","') > 0 THEN
                TRIM(
                    SUBSTR(
                        remaining,
                        INSTR(remaining, '","') + 3,
                        INSTR(SUBSTR(remaining, INSTR(remaining, '","') + 3), '","') - 1
                    ),
                    '"'
                )
            ELSE TRIM(remaining, '"')
        END AS category
    FROM split_cats
    WHERE remaining != ''
),
sorted_cats AS (
    -- 自连接生成每个类别的排序位置：统计当前ID下比当前类别小的类别数量
    SELECT
        sc1.id,
        sc1.category,
        COUNT(sc2.category) AS sort_order
    FROM split_cats sc1
    LEFT JOIN split_cats sc2 
        ON sc1.id = sc2.id 
        AND sc2.category <= sc1.category
    GROUP BY sc1.id, sc1.category
),
concat_cats AS (
    -- 初始拼接：取每个ID下排序第一的类别
    SELECT
        id,
        category AS combined,
        sort_order
    FROM sorted_cats
    WHERE sort_order = 1
    
    UNION ALL
    
    -- 递归拼接：按排序位置依次拼接后续类别
    SELECT
        cc.id,
        cc.combined || '","' || sc.category AS combined,
        sc.sort_order
    FROM concat_cats cc
    JOIN sorted_cats sc 
        ON cc.id = sc.id 
        AND sc.sort_order = cc.sort_order + 1
)
-- 最终组合结果，处理空类别/NULL的情况
SELECT
    b.id,
    b.categories AS original_categories,
    '["' || cc.combined || '"]' AS sorted_categories
FROM books b
LEFT JOIN concat_cats cc ON b.id = cc.id
WHERE cc.sort_order = (
    SELECT MAX(sort_order) 
    FROM sorted_cats sc 
    WHERE sc.id = b.id
)

UNION ALL

SELECT
    id,
    categories,
    categories
FROM books
WHERE categories IS NULL OR categories = '[]'

ORDER BY id;

关键步骤说明 #

• split_cats CTE：将每行的Categories字段拆分为单个类别条目，去除外层的方括号和每个类别两端的双引号。
• sorted_cats CTE：通过自连接统计每个类别在当前行中的排序位置（首字母从小到大），解决3.4.4无窗口函数的限制。
• concat_cats CTE：按照生成的排序位置递归拼接类别，确保最终拼接结果是按首字母排序的。
• 最终查询：将拼接后的类别恢复为原格式（方括号包裹、双引号分隔），同时兼容空类别或NULL的情况。

注意事项 #

1. 确保books表有唯一标识每行的字段（示例中用id），如果没有，需要替换为能唯一区分行的字段组合。
2. 该方案适用于500+行的小规模数据，性能足以满足需求。
3. 若类别中包含特殊字符（比如","），需要调整拆分逻辑，但按你描述的格式，该方案可直接使用。

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