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

select
    case
        when profile like '%female' then 'female'
        else 'male'
    end as 'gender',
    count(*) as number
from
    user_submit
group by
    gender

方法一：case when

select
    substring_index(profile,',',-1)  as 'gender',
    count(*) as number
from
    user_submit
group by
    gender

方法二：SUBSTRING_INDEX(str, delim, count) 提取性别信息

• str：要提取子字符串的原始字符串
• delim：分隔符，用于确定子字符串的位置
• count：指定要返回的子字符串的索引【此处负数从后往前】

select
    if(profile like '%female', 'female', 'male') as 'gender',
    count(*) as number
from
    user_submit
group by
    gender

方法三：if(profile like '%female', 'female', 'male') 模糊匹配

• if(profile like '%female', 'female', 'male'): 这是一个条件表达式，检查 profile 字段的值是否包含字符串 'female'。如果包含，则返回 'female'，否则返回 'male'。