MySQL查询：基于service_join值统计多表Count与Sum

先明确表结构与测试数据（方便验证） #

假设三张表的结构及测试数据如下：

SERVICE_NAMES（活动表）

ATTENDANCE（个人参与者表）

VISITOR_ATTENDANCE（访客分组统计表）

MySQL 8.0+ 版本查询语句（支持递归CTE） #

SELECT
  sn.*,
  -- 计算关联活动的总参与人数：个人参与者数 + 访客分组计数之和
  COALESCE(
    (
      SELECT SUM(a_total) + SUM(v_total)
      FROM (
        -- 递归拆分service_join中的活动ID集合
        WITH RECURSIVE split_service_ids AS (
          SELECT
            sn.ID AS parent_id,
            TRIM(SUBSTRING_INDEX(sn.service_join, ',', 1)) AS service_id,
            TRIM(SUBSTRING(sn.service_join, LENGTH(SUBSTRING_INDEX(sn.service_join, ',', 1)) + 2)) AS remaining_ids
          FROM SERVICE_NAMES sn
          WHERE sn.service_join IS NOT NULL AND sn.service_join != ''
          UNION ALL
          SELECT
            parent_id,
            TRIM(SUBSTRING_INDEX(remaining_ids, ',', 1)) AS service_id,
            TRIM(SUBSTRING(remaining_ids, LENGTH(SUBSTRING_INDEX(remaining_ids, ',', 1)) + 2)) AS remaining_ids
          FROM split_service_ids
          WHERE remaining_ids IS NOT NULL AND remaining_ids != ''
        )
        SELECT
          COUNT(a.ID) AS a_total,
          COALESCE(SUM(v.visitor_count), 0) AS v_total
        FROM split_service_ids si
        LEFT JOIN ATTENDANCE a ON a.service_name = si.service_id
        LEFT JOIN VISITOR_ATTENDANCE v ON v.service_name = si.service_id
        WHERE si.parent_id = sn.ID
        GROUP BY si.parent_id
      ) AS temp
    ), 0
  ) AS total_attendees,
  -- 当前活动的个人参与者数
  (SELECT COUNT(*) FROM ATTENDANCE a WHERE a.service_name = sn.ID) AS attendance,
  -- 当前活动的访客分组计数（无数据则返回NULL）
  (SELECT v.visitor_count FROM VISITOR_ATTENDANCE v WHERE v.service_name = sn.ID) AS visitors
FROM SERVICE_NAMES sn;

MySQL 5.x 版本兼容方案（无递归CTE） #

如果你的MySQL版本不支持递归CTE，可以用数字辅助表拆分ID集合：

1. 先创建一个数字辅助表（按需调整数字数量，覆盖service_join中最多的ID个数）：

CREATE TABLE IF NOT EXISTS numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5);

2. 替换后的查询语句：

SELECT
  sn.*,
  COALESCE(
    (
      SELECT SUM(a_total) + SUM(v_total)
      FROM (
        SELECT
          sn.ID AS parent_id,
          TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(sn.service_join, ',', n.n), ',', -1)) AS service_id
        FROM SERVICE_NAMES sn
        JOIN numbers n ON n.n <= LENGTH(sn.service_join) - LENGTH(REPLACE(sn.service_join, ',', '')) + 1
        WHERE sn.service_join IS NOT NULL AND sn.service_join != ''
      ) AS si
      LEFT JOIN ATTENDANCE a ON a.service_name = si.service_id
      LEFT JOIN VISITOR_ATTENDANCE v ON v.service_name = si.service_id
      WHERE si.parent_id = sn.ID
      GROUP BY si.parent_id
    ), 0
  ) AS total_attendees,
  (SELECT COUNT(*) FROM ATTENDANCE a WHERE a.service_name = sn.ID) AS attendance,
  (SELECT v.visitor_count FROM VISITOR_ATTENDANCE v WHERE v.service_name = sn.ID) AS visitors
FROM SERVICE_NAMES sn;

关键逻辑说明 #

• 拆分service_join集合：通过递归CTE（MySQL8+）或数字辅助表，把逗号分隔的活动ID拆分成单独的行，才能关联统计每个关联活动的参与数据。
• total_attendees计算：汇总所有关联活动的个人参与者行数和访客分组计数之和，用COALESCE处理NULL场景，确保无关联活动时返回0。
• attendance/visitors计算：直接通过子查询关联当前活动ID，得到对应的数据。

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