题解 | #统计每种性别的人数#

WITH RECURSIVE
    cte AS (
        SELECT
            device_id,
            SUBSTRING_INDEX (profile, ',', 1) AS part,
            SUBSTRING(profile, INSTR (profile, ',') + 1) AS remaining_string,
            1 AS level
        FROM
            user_submit
        WHERE
            profile IS NOT NULL
            AND profile <> ''
        UNION ALL
        SELECT
            device_id,
            SUBSTRING_INDEX (remaining_string, ',', 1) AS part,
            IF (
                INSTR (remaining_string, ',') > 0,
                SUBSTRING(
                    remaining_string,
                    INSTR (remaining_string, ',') + 1
                ),
                ''
            ) AS remaining_string,
            level + 1
        FROM
            cte
        WHERE
            remaining_string IS NOT NULL
            AND remaining_string <> ''
    )
SELECT
    part AS gender,
    COUNT(*)
FROM
    cte
WHERE
    part IN ('male', 'female')
GROUP BY
    gender;