题解 | 统计所有课程参加培训人次

select 
  count(*) staff_nums
 from 
(with recursive
    splitter as (
        select
            info_id,
            staff_id,
            course,
            1 as pos, -- 起始位置
            -- 提取第一个课程
          substring_index(substring_index(course, ',', 1), ',', -1) as single_course,
            -- 计算课程总数
            case
                when course is null then 0
                else length(course) - length(
                    replace(course, ',', '')
                ) + 1
            end as total_courses
        from
            cultivate_tb
        where
            course is not null -- 过滤null值
        union all
        select
            s.info_id,
            s.staff_id,
            s.course,
            s.pos + 1, -- 位置递增
            -- 提取下一个课程
            substring_index(
                substring_index(s.course, ',', s.pos + 1),
                ',',
                -1
            ),
            s.total_courses -- 保持课程总数不变
        from
            splitter s
        where
            s.pos < s.total_courses -- 递归终止条件
    )
    select
    staff_id,
    info_id,
    single_course as course
from
    splitter
    order by staff_id
    ) t