如何编写SQLite代码实现显示指定代码N09及其关联分组聚合代码并修复计数异常问题

你的现有代码存在几个关键逻辑问题，导致无法生成目标结果，我来帮你梳理并给出正确的解决方案：

现有代码的核心问题 #

1. WHERE子句过滤过度：where Code in ('N09')直接排除了同一学生组内的其他关联代码（比如M33、Y03），这就导致group_concat只能拿到N09，计数自然也只会是1。
2. GROUP BY逻辑错误：你把聚合计算后的'Count'放到了GROUP BY子句中，这不符合SQL规范，而且分组维度没有覆盖需要聚合的完整场景。
3. 筛选逻辑错位：我们需要先识别出包含N09且Status_Complete为yes的学生组（按Student_ID、Status_Date、Status_TIME分组），而不是先过滤掉其他代码。

正确的SQLite代码 #

CREATE TABLE table2 AS
WITH student_valid_groups AS (
    -- 第一步：筛选出符合条件的学生组，并计算每组的代码总数和完整代码拼接
    SELECT 
        Student_ID,
        Status_Complete,
        Status_Date,
        Status_TIME,
        COUNT(Code) AS total_count,
        GROUP_CONCAT(Code) AS all_assigned_codes
    FROM table1
    WHERE Status_Complete = 'yes'
    GROUP BY Student_ID, Status_Complete, Status_Date, Status_TIME
    -- 确保该组包含N09代码
    HAVING SUM(CASE WHEN Code = 'N09' THEN 1 ELSE 0 END) > 0
)
-- 第二步：关联回原表，只保留每组中Code为N09的记录，并带上统计值
SELECT 
    sg.Student_ID,
    sg.Status_Complete,
    sg.Status_Date,
    sg.Status_TIME,
    t1.Code,
    sg.total_count AS 'Count',
    sg.all_assigned_codes AS 'Group_Concat(Code)'
FROM student_valid_groups sg
JOIN table1 t1 
    ON sg.Student_ID = t1.Student_ID
    AND sg.Status_Date = t1.Status_Date
    AND sg.Status_TIME = t1.Status_TIME
    AND t1.Code = 'N09'
ORDER BY sg.Student_ID;

代码逻辑说明 #

1. CTE student_valid_groups：
   • 先对所有Status_Complete='yes'的记录按学生ID、状态日期、状态时间分组，计算每组的代码总数total_count和所有代码的拼接结果all_assigned_codes。
   • 通过HAVING SUM(CASE WHEN Code = 'N09' THEN 1 ELSE 0 END) > 0筛选出包含N09代码的组，确保只有符合要求的组被保留。
2. 关联查询：
   • 将筛选后的学生组与原表关联，只取出每组中Code为N09的记录，同时带上之前计算好的总数和完整代码拼接结果，完美匹配你想要的目标表结构。

最终生成的table2结果 #

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