转载于:https://www.cnblogs.com/mqxs/p/7380933.html
https://www.nowcoder.com/practice/f04189f92f8d4f6fa0f383d413af7cb8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
select substring_index(profile, ",", -1) as gender, count(device_id) as number from user_submit group by gender;
或
select if(profile like "%female", "female", "male") as gender, count(device_id) as number from user_submit group by gender;
https://www.nowcoder.com/practice/26c8715f32e24d918f15db69518f3ad8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
4种方法:
select -- 替换法 replace(string, '被替换部分','替换后的结果') -- device_id, replace(blog_url,'http:/url/','') as user_name -- 截取法 substr(string, start_point, length*可选参数*),其中start_point从1开始计数。 -- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam -- 删除法 trim('被删除字段' from 列名) -- device_id, trim('http:/url/' from blog_url) as user_name -- 字段切割法 substring_index(string, '切割标志', 位置数(负号:从后面开始)) device_id, substring_index(blog_url,'/',-1) as user_name from user_submit;
select device_id, # replace(blog_url,'http:/url/','') as user_name # substr(blog_url, 11, length(blog_url)-10) as user_name # substr=substring # trim("http:/url/" from blog_url) as user_name substring_index(blog_url, "/", -1) as user_name from user_submit;
https://www.nowcoder.com/practice/b8d8a87fe1fc415c96f355dc62bdd12f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:
select substring_index(substring_index(profile, ",", -2), ",", 1) as age, # 例如,先截取后2个字段:“27,male”,再截取其第一个字段,即为所得的age. count(*) as number from user_submit group by age;
法2:
select substring(profile, 12 ,2) as age, # 从第12个元素开始,截取2个元素。 # substr(profile, 12 ,2) as age, count(*) as number from user_submit group by age;