1.substring_index函数:拆分字符串
格式:substring_index(str,delim,count)
说明:substring_index(被截取字段,关键字,关键字出现的次数)
注:如果关键字出现的次数是负数,则是从后倒数,到字符串结束
例如:user表,需统计多少男人多少女人
id | profile |
---|---|
1 | 180cm,75cm,27,male |
2 | 175cm,75cm,27,female |
3 | 185cm,75cm,27,male |
select gender , count(gender) as number from
(
select substring_index(profile,',',-1) as gender from user
) t1
group by gender