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

SELECT
    gender,
    COUNT( device_id) AS number
FROM (
    SELECT
        device_id,
        -- 提取最后一个逗号之后的内容作为性别
        SUBSTRING_INDEX(profile, ',', -1) AS gender
    FROM user_submit
) AS tmp
GROUP BY gender
ORDER BY CASE gender WHEN 'male' THEN 1 WHEN 'female' THEN 2 END

• SUBSTRING_INDEX(profile, ',', -1)：对profile字符串按“，”分割后取第-1个（最后一个部分）
• COUNT(distinct device_id) 加上distinct报错
• ORDER BY CASE gender WHEN 'male' THEN 1 WHEN 'female' THEN 2 END： groupby是按字符编码排序，要指定排序

select
gender,
count( device_id) as number
from(
select 
device_id,
case 
when right(profile,5) = ',male' then 'male' 
when right(profile,6) = 'female' then 'female' 
else 'unknown' 
end as gender
from user_submit
) as tmp
group by gender
ORDER BY CASE gender WHEN 'male' THEN 1 WHEN 'female' THEN 2 ENd

• right(profile,5)：取profile字符串的后5个字符