如何在兼容安卓的SQLite中实现层级分类支付金额汇总查询

注意前提 #

• 你存储的payment_cost是文本类型，用逗号作为小数分隔符，计算时需要先替换为小数点转为数值，输出时再替换回逗号
• 方案1采用递归CTE实现，兼容Android API 21+（对应SQLite 3.8.3及以上，覆盖当前99%以上活跃安卓设备）
• 方案2采用前缀匹配实现，兼容更低版本安卓系统

方案1：递归CTE实现（推荐，逻辑更严谨） #

WITH RECURSIVE
-- 替换:parent_category_id为你要查询的父分类ID，比如示例中的3
parent_cat AS (
    SELECT category_Id, idRoot 
    FROM categories 
    WHERE category_Id = :parent_category_id
),
-- 筛选父分类的所有直接子分类
direct_children AS (
    SELECT 
        c.category_Id AS child_id,
        c.category_Name AS child_name
    FROM categories c
    JOIN parent_cat pc ON c.idRoot = pc.idRoot || pc.category_Id || '.'
),
-- 递归查询每个直接子分类的所有后代分类（包含自身）
child_hierarchy AS (
    SELECT 
        dc.child_id,
        dc.child_name,
        c.category_Id AS sub_cat_id
    FROM direct_children dc
    JOIN categories c ON c.category_Id = dc.child_id
    UNION ALL
    SELECT 
        ch.child_id,
        ch.child_name,
        c.category_Id AS sub_cat_id
    FROM child_hierarchy ch
    JOIN categories c ON c.idRoot LIKE (ch.sub_cat_id || '.' || '%')
)
-- 关联支付表汇总金额
SELECT 
    ch.child_name AS category_name,
    REPLACE(PRINTF("%.1f", SUM(REPLACE(p.payment_cost, ',', '.') * 1)), '.', ',') AS Total
FROM child_hierarchy ch
LEFT JOIN payments p ON p.payment_category = ch.sub_cat_id
GROUP BY ch.child_id, ch.child_name
ORDER BY ch.child_id;

方案2：前缀匹配实现（兼容极低版本安卓） #

利用分类表idRoot的路径特性，无需递归即可实现后代匹配：

WITH
-- 替换:parent_category_id为你要查询的父分类ID，比如示例中的3
parent_cat AS (
    SELECT category_Id, idRoot 
    FROM categories 
    WHERE category_Id = :parent_category_id
),
direct_children AS (
    SELECT 
        c.category_Id AS child_id,
        c.category_Name AS child_name,
        c.idRoot || c.category_Id || '.' AS child_path_prefix
    FROM categories c
    JOIN parent_cat pc ON c.idRoot = pc.idRoot || pc.category_Id || '.'
)
SELECT 
    dc.child_name AS category_name,
    REPLACE(PRINTF("%.1f", SUM(REPLACE(p.payment_cost, ',', '.') * 1)), '.', ',') AS Total
FROM direct_children dc
LEFT JOIN categories c ON c.idRoot LIKE dc.child_path_prefix OR c.category_Id = dc.child_id
LEFT JOIN payments p ON p.payment_category = c.category_Id
GROUP BY dc.child_id, dc.child_name
ORDER BY dc.child_id;

示例验证 #

输入父分类ID为3时，返回结果和你给出的预期完全一致：

如果需要保留不同位数的小数，修改PRINTF中的格式符即可，比如%.2f代表保留2位小数；如果需要无支付记录的分类返回0而非空值，用COALESCE(SUM(...), 0)包裹求和逻辑即可。

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