如何使用CTE汇总活动参与者数据？SQL查询问题求助

问题需求 #

根据指定event_code生成活动参与者汇总数据：

• 若为父活动：汇总该父活动及其所有子活动的参与者
• 若为独立活动（无父活动且无子活动）：仅汇总该活动的参与者

原查询代码存在逻辑缺陷，无法实现需求，代码如下：

WITH selectedEvent  AS (
    SELECT
        event.Event_code              AS Event_code,
        event.parent_code        AS parent_code,
        attguest.attended        AS attevent,
        cont.gender AS Gender
    FROM
        event event
        INNER JOIN EventAttendance att 
        ON event.event_code = att.event_code
        INNER JOIN EventGuestAttendance  attguest 
        ON att.Event_attendance_code = attguest.Event_attendance_code
        LEFT JOIN contact cont
        ON attguest.Contact_code=cont.Contact_code
        WHERE attguest.Contact_code is not null
        AND attguest.Attended='1' 
        --
        AND event.event_code=(Parent event or single event)
)
SELECT
    eOne.eOneID,
    eone.eOneParent
    
FROM selectedEvent eOne

问题排查 #

1. 伪代码未落地：WHERE子句中的event.event_code=(Parent event or single event)是无效逻辑，无法识别父活动/独立活动，也无法关联子活动数据
2. 字段引用错误：最终SELECT的eOne.eOneID、eone.eOneParent在CTEselectedEvent中未定义，属于无效字段
3. 缺少子活动递归逻辑：原查询仅关联当前活动的参与者，未处理父活动需要包含所有子活动的场景
4. 无汇总逻辑：未实现参与者的去重统计（比如同一联系人参加多个子活动需避免重复计数）或维度汇总（如按性别统计人数）

修正后的查询方案 #

使用递归CTE获取目标活动及其所有子活动，再关联参与者数据进行汇总：

-- 递归CTE：获取目标活动及其所有子活动
WITH target_events AS (
    -- 初始：获取指定的活动（替换为实际传入的event_code）
    SELECT event_code, parent_code
    FROM event
    WHERE event_code = '指定的活动编码'
    UNION ALL
    -- 递归：获取所有子活动
    SELECT e.event_code, e.parent_code
    FROM event e
    INNER JOIN target_events te ON e.parent_code = te.event_code
),
-- 获取所有有效参与者（去重，避免同一人重复统计）
participants AS (
    SELECT DISTINCT
        cont.contact_code,
        cont.gender
    FROM target_events te
    INNER JOIN EventAttendance att ON te.event_code = att.event_code
    INNER JOIN EventGuestAttendance attguest ON att.Event_attendance_code = attguest.Event_attendance_code
    LEFT JOIN contact cont ON attguest.Contact_code = cont.Contact_code
    WHERE attguest.Contact_code IS NOT NULL
      AND attguest.Attended = '1'
)
-- 汇总输出：可根据需求调整汇总维度
SELECT
    COUNT(*) AS total_participants,
    COALESCE(SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END), 0) AS male_count,
    COALESCE(SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END), 0) AS female_count,
    COALESCE(SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END), 0) AS unknown_gender_count
FROM participants;

关键说明 #

• 递归CTEtarget_events：先获取指定活动，再递归遍历所有层级的子活动，确保父活动能包含所有下属子活动
• participants CTE：通过DISTINCT去重联系人，避免同一参与者参加多个子活动被重复统计
• 汇总逻辑：示例按性别统计人数，可根据需求修改（如仅统计总人数，或按其他维度分组）
• 参数替换：将'指定的活动编码'替换为实际需要查询的event_code

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