WebSql中如何使用子查询统计组及其子组的关联日志总记录数

报错原因说明 #

你修改语句时触发的列数不匹配错误，核心是**IN 运算符要求子查询仅返回单列值用于匹配**，一旦在子查询中同时返回logId和count两列，数据库无法判断匹配逻辑，因此抛出语法错误。
结合你现有基于嵌套集（LEFT/RIGHT字段）的组层级存储逻辑，无需重写原有结构，仅需提前聚合每个日志的记录数，再按子树范围累加即可实现需求。

改造后的可运行查询 #

WITH logRecordCounts AS (
    -- 先按日志ID聚合，算出每个有有效记录的日志对应的总记录数
    SELECT 
        logId, 
        COUNT(*) AS recordCount 
    FROM savedLogRecords
    GROUP BY logId
),
groupDirectRecordCounts AS (
    -- 关联日志表，算出每个组直接挂载的有效日志的总记录数
    SELECT 
        l.groupId,
        SUM(lrc.recordCount) AS directRecordCount
    FROM logs l
    INNER JOIN logRecordCounts lrc 
        ON l.logId = lrc.logId
    GROUP BY l.groupId
)
SELECT 
    G.groupId,
    -- 累加当前组整棵子树范围内的所有记录数，无匹配时返回0
    COALESCE((
        SELECT SUM(gdrc.directRecordCount)
        FROM groups G2
        INNER JOIN groupDirectRecordCounts gdrc 
            ON G2.groupId = gdrc.groupId
        WHERE G2.LEFT BETWEEN G.LEFT AND G.RIGHT
    ), 0) AS totalSubtreeRecordCount
FROM groups G
WHERE 
    G.parentId = ?
    -- 保留原有过滤逻辑：仅返回子树内存在有效记录的组
    AND EXISTS (
        SELECT 1
        FROM groups G2
        INNER JOIN groupDirectRecordCounts gdrc 
            ON G2.groupId = gdrc.groupId
        WHERE G2.LEFT BETWEEN G.LEFT AND G.RIGHT
    )

逻辑匹配验证 #

完全匹配你给出的层级示例计算规则：

• 传入顶级组ID时：子树覆盖组结构内所有节点，累加范围内日志X（3条）、日志Y（4条）、日志Z（5条）的记录数，结果为12
• 传入子组B的ID时：子树仅覆盖子组B、子组C节点，累加范围内日志Y（4条）、日志Z（5条）的记录数，结果为9
• 原查询“返回有有效记录的组ID列表”的逻辑完全保留，仅新增子树总记录数列。

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