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

-- 01 SUBSTRING_INDEX（str,delim,count） str要处理的字符串，delim分隔符，count取几个：正数从左到右，复数从右到左
SELECT 
SUBSTRING_INDEX(profile ,',',-1) as gender,
COUNT(1) as number 
from user_submit 
GROUP BY gender;

-- 02 INSTR(str,substr) 返回substr在str中第一次出现的位置，未出现则为0
-- 这里数据特点，取female是否出现判断
SELECT 
case when  INSTR(profile ,'female')>0 then 'female' 
     else 'male' end as gender,
COUNT(1) as number 
from user_submit 
GROUP BY gender;

-- 03  IF(expr1,expr2,expr3) if exper1为true则返回exper2的值，false则返回exper3的值
select IF(profile like '%female','female','male') as gender,
COUNT(1) as number
from user_submit
GROUP BY gender