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

WITH RECURSIVE split_data AS (
    -- 基础查询：获取第一个元素和剩余字符串
    SELECT
        staff_id,
        TRIM(SUBSTRING_INDEX(course, ',', 1)) AS item,
        SUBSTRING(course, LENGTH(SUBSTRING_INDEX(course, ',', 1)) + 2) AS remaining
    FROM cultivate_tb
    WHERE course IS NOT NULL AND course != ''
    
    UNION ALL
    
    -- 递归查询：处理剩余字符串
    SELECT
        staff_id,
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
    FROM split_data
    WHERE remaining != ''
)
,tmp_step2 as (
SELECT staff_id, item 
FROM split_data 
# ORDER BY staff_id
)
select count(1) as staff_nums
from tmp_step2

运用了递归查询，MySQL中没有炸裂函数，如果有的话，也就不用递归了。。