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

#搭配like的方法
SELECT
  CASE WHEN profile like '%,male' THEN 'male'
       WHEN profile like '%,female' THEN 'female'
       ELSE 'other'
  END AS gender,
  COUNT(*) AS number
FROM
  user_submit
GROUP BY
  gender




# 搭配正则化
# SELECT
#   CASE WHEN profile REGEXP ',[^,]*,male$' THEN 'male'
#        WHEN profile REGEXP ',[^,]*,female$' THEN 'female'
#        ELSE 'other'
#   END AS gender,
#   COUNT(*) AS number
# FROM
#   user_submit
# GROUP BY
#   gender



# 搭配子字符串
# SELECT  DISTINCT(substring_index(profile,',',-1)) AS gender, 
# COUNT(substring_index(profile,',',-1)) AS number
# FROM user_submit GROUP BY gender ;