题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个性别的用户分别有多少参赛者,请取出相应结果
示例: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
输入:
drop table if exists user_submit; CREATE TABLE `user_submit` ( `id` int NOT NULL, `device_id` int NOT NULL, `profile` varchar(100) NOT NULL, `blog_url` varchar(100) NOT NULL ); INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777'); INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc'); INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer'); INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd'); INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
复制输出:
male|2 female|3
本题涉及了利用正则表示进行匹配的函数regexp_substr()函数。
格式:regexp_substr(string,pattern,position,occurence,[modifier])
string:需要进行处理的字符串
pattern:匹配的正则表达式
occurrence:获取第几组通过正则表达式分割出来的组。
modifier:可选项,模式,‘i’不区分大小写,‘c’区分大小写。
select
gender,
count(*) number
from
(select
regexp_substr(profile,'[^,]+',1,4) gender
from
user_submit) a
group by
gender;