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

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

substring_index (列名, 参照物, 位置）：从一个元素的字符串中提取指定位置的文字，SUBSTRING_INDEX(str, delimiter, count)

• str：需要操作的列
• delimiter：参照物，分隔符
• count：第几个参照物，正数的时候从左向右数，负数的时候从右向左数
• 返回的是一个字符串

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

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

case的代码示例

SELECT 
    column_name,
    CASE column_name
        WHEN 值 THEN result1
        WHEN 值 THEN result2
        ...
        ELSE default_result
    END
FROM table_name;

SELECT 
    column_name,
    CASE
        WHEN 条件 THEN result1
        WHEN 条件 THEN result2
        ...
        ELSE default_result
    END
FROM table_name;

SELECT
    order_id,
    CASE status
        WHEN 'pending' THEN '待处理'
        WHEN 'completed' THEN '已完成'
        ELSE '未知状态'
    END
FROM orders;

SELECT
    product_name,
    CASE
        WHEN price < 50 THEN '便宜'
        WHEN price BETWEEN 50 AND 100 THEN '适中'
        WHEN price > 100 THEN '昂贵'
        ELSE '未知价格'
    END
FROM products;