题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个性别的用户分别有多少参赛者,请取出相应结果
示例:user_submit
device_id | profile | blog_url |
2138 | 180cm,75kg,27,male | http:/url/bigboy777 |
3214 | 165cm,45kg,26,female | http:/url/kittycc |
6543 | 178cm,65kg,25,male | http:/url/tiger |
4321 | 171cm,55kg,23,female | http:/url/uhksd |
2131 | 168cm,45kg,22,female | http:/urlsydney |
根据示例,你的查询应返回以下结果:
gender | number |
male | 2 |
female | 3 |
解题:
# 方式一:
# 1.用子查询模拟出想要的结果(将字段值用逗号进行分割)
# 2.在分组后采取COUNT函数统计数量
SELECT
table2.profile,
COUNT(table2.profile) AS number
FROM
(
SELECT
device_id,
SUBSTRING_INDEX(profile, ',', -1) AS profile
FROM
user_submit
) AS table2
GROUP BY table2.profile;
# 方式二:
# 1.在分组时就进行结果分割,再采用COUNT函数统计数量
SELECT
SUBSTRING_INDEX(profile, ',', -1) AS profile,
COUNT(*) AS number
FROM
user_submit
GROUP BY SUBSTRING_INDEX(profile, ',', -1);
结果:
小结:学习SUBSTRING_INDEX()函数;