SQL复习笔记

文本函数-substring_index

substring_index('http/a/1/s','/',1)

SUBSTRING_INDEX函数用于将字符串依据某个指定分隔符进行切分，并返回指定位置分隔符前的字符。(字段分割符,位置）

假如现在有一列字段以字符串记录了用户的身高体重和性别，如 ‘180,78kg,male'，现 在想要分别取出用户的身高和体重，应该怎么做呢？这时就可以用到 SUBSTRING_INDEX函数，指定逗号作为分隔符，如果想要返回身高的话，位置填1，即 可得到相应结果。

select SUBSTRING_INDEX('180,78kg',',','1') as height

取身高相对简单，但如果想要取出体重应该怎么做呢？如果使用下面的代码，结果会返回第二个分隔符前的所有字符，结果为’180,78kg’，会多取出身高的信息。

select SUBSTRING_INDEX('180,78kg',',’,'2') as height

这种情况下可以嵌套一次SUBSTRING_INDEX查询,负数位置代表从后向前取，-1代表最后一位，第二层嵌套相当于是取出’180,78kg'的最后一个逗号后的字符，得到我们想要的体重数据

select SUBSTRING_INDEX(SUBSTRING_INDEX('180,78kg',',’,'2’),’,’,-1) as height

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

示例：user_submit

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/ur/bisdgboy777
3214	165cm,45kg,26,female	http:/url/dkittycc
6543	178cm,65kg,25,male	http:/ur/tigaer
4321	171 cm,55kg,23,female	http:/url/uhksd
2131	168cm,45kg,22,female	http:/url/sydney

根据示例，你的查询应返回以下结果：

device_id	user_name
2138	bisdgboy777
3214	dkittycc
6543	tigaer
4321	uhsksd
2131	sydney

select
  device_id,
  substring_index(blog_url, '/', -1) as user_name
from
  user_submit

题目2

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

示例：user_submit

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/ur/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:/url/sydney

根据示例，你的查询应返回以下结果：

age	number
27	1
26	1
25	1
23	1
22	1

select
  substring_index(substring_index(profile, ',', 3), ',', -1) as age,
  count(device_id)
from
  user_submit
group by
  age