本文总结了牛客SQL刷题：非技术快速入门的进阶知识，阅读本文可以轻松解决这个系列下的难题。

字符串的切割、截取、删除、替换

字符串切割

substring_index()可以按照给定的分隔符对字符串进行切割。其语法为：

substring_index(str, delim, count)

参数如下：

  • str：需要拆分的字符串
  • delim：分隔符，通过某字符进行拆分
  • count：当 count为正数，取第n个分隔符之前的所有字符； 当count为负数，取倒数第n个分隔符之后的所有字符。

例子：

mysql> select substring_index('www.wikidm.cn', '.', 1);
+------------------------------------------+
| substring_index('www.wikidm.cn', '.', 1) |
+------------------------------------------+
| www                                      |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('www.wikidm.cn', '.', 2);
+------------------------------------------+
| substring_index('www.wikidm.cn', '.', 2) |
+------------------------------------------+
| www.wikidm                               |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('www.wikidm.cn', '.', 3);
+------------------------------------------+
| substring_index('www.wikidm.cn', '.', 3) |
+------------------------------------------+
| www.wikidm.cn                            |
+------------------------------------------+
1 row in set (0.00 sec)

若是count为负呢？看下面：

mysql> select substring_index('www.wikidm.cn', '.', -1);
+-------------------------------------------+
| substring_index('www.wikidm.cn', '.', -1) |
+-------------------------------------------+
| cn                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('www.wikidm.cn', '.', -2);
+-------------------------------------------+
| substring_index('www.wikidm.cn', '.', -2) |
+-------------------------------------------+
| wikidm.cn                                 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('www.wikidm.cn', '.', -3);
+-------------------------------------------+
| substring_index('www.wikidm.cn', '.', -3) |
+-------------------------------------------+
| www.wikidm.cn                             |
+-------------------------------------------+
1 row in set (0.00 sec)

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容；相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。

如果要中间的的wikidm怎么办？很简单，从左数第二个分隔符的左边全部，再从右数的第一个分隔符的右边即可。

mysql> select substring_index(substring_index('www.wikidm.cn', '.', 2),'.', -1);
+-------------------------------------------------------------------+
| substring_index(substring_index('www.wikidm.cn', '.', 2),'.', -1) |
+-------------------------------------------------------------------+
| wikidm                                                            |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

现在来一个复杂一点的例子，也就是题目：SQL30 统计每种性别的人数。

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

sql server去除字符串中的符号 sql字符串去掉指定字符_字符串

方法一：使用substring_index把性别取出来就行了。

select
  substring_index(profile, ',', -1) as gender,
  count(*) as number
from
  user_submit
group by
  gender;

方法二：模糊匹配(like)

select
  case
    when profile like '%female' then 'female'
    when profile like '%male' then 'male'
  end as gender,
  count(*) as number
from
  user_submit
group by
  gender;

字符串截取

left()

从左边开始截取字符串，其语法为：

left(str, len)

参数说明：

  • str：被截取的字符串
  • len：正整数，表示截取字符串从最左边开始到第len位的值。

例子：

mysql> select left('4794,3536,3565,7854', 5);
+--------------------------------+
| left('4794,3536,3565,7854', 5) |
+--------------------------------+
| 4794,                          |
+--------------------------------+
1 row in set (0.00 sec)

right()与left()一样，只是从右边开始截取字符串。例如：

mysql> select right('4794,3536,3565,7854', 5);
+---------------------------------+
| right('4794,3536,3565,7854', 5) |
+---------------------------------+
| ,7854                           |
+---------------------------------+
1 row in set (0.00 sec)

substring()

语法格式：

substring(str, pos, len)

参数说明：

  • str：被截取的字符串
  • pos：从第几位开始截取，当pos为正数时，表示从字符串开始第pos位开始取，直到结束；当pos为负数时，表示从字符串倒数第pos位开始取，直到结束。
  • len：表示取几个字符。

例子：从第3个字符开始获取字符串：

mysql> select substring('4794,3536,3565,7854', 3);
+-------------------------------------+
| substring('4794,3536,3565,7854', 3) |
+-------------------------------------+
| 94,3536,3565,7854                   |
+-------------------------------------+
1 row in set (0.00 sec)

从倒数第2个字符开始获取字符串：

mysql> select substring('4794,3536,3565,7854', -2);
+--------------------------------------+
| substring('4794,3536,3565,7854', -2) |
+--------------------------------------+
| 54                                   |
+--------------------------------------+
1 row in set (0.00 sec)

从字符串的第 4 个字符位置开始取，只取 2 个字符。

mysql> select substring('4794,3536,3565,7854', 4, 2);
+----------------------------------------+
| substring('4794,3536,3565,7854', 4, 2) |
+----------------------------------------+
| 4,                                     |
+----------------------------------------+
1 row in set (0.00 sec)

从字符串的倒数第 4 个字符位置开始取，只取 2 个字符。

mysql> select substring('4794,3536,3565,7854', -4, 2);
+-----------------------------------------+
| substring('4794,3536,3565,7854', -4, 2) |
+-----------------------------------------+
| 78                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

注意：在函数substring()中， pos可以是负值，但len不能取负值。

字符串替换

替换字符串，其语法为：

replace(str, from_str, to_str)

参数说明：

  • str：需要进行替换的字符串
  • from_str：需要被替换的字符串
  • to_str：需要替换的字符串

例子：将分隔符",“逗号替换为”"。

mysql> select replace('4794,3536,3565,7854', ',', '');
+-----------------------------------------+
| replace('4794,3536,3565,7854', ',', '') |
+-----------------------------------------+
| 4794353635657854                        |
+-----------------------------------------+
1 row in set (0.00 sec)

字符串删除

删除字符串中的某个字段，其语法为：

trim('被删除字段' from 列名)

举个例子，下面代码来自：SQL31 提取博客URL中的用户名。原来的数据为：

mysql> select * from user_submit;
+----+-----------+----------------------+-----------------------+
| id | device_id | profile              | blog_url              |
+----+-----------+----------------------+-----------------------+
|  1 |      2138 | 180cm,75kg,27,male   | http:/url/bisdgboy777 |
|  1 |      3214 | 165cm,45kg,26,female | http:/url/dkittycc    |
|  1 |      6543 | 178cm,65kg,25,male   | http:/url/tigaer      |
|  1 |      4321 | 171cm,55kg,23,female | http:/url/uhsksd      |
|  1 |      2131 | 168cm,45kg,22,female | http:/url/sysdney     |
+----+-----------+----------------------+-----------------------+
5 rows in set (0.00 sec)

下面通过删除每个链接中的公共部分来提取用户名：

mysql> select trim('http:/url/' from blog_url) from user_submit;
+----------------------------------+
| trim('http:/url/' from blog_url) |
+----------------------------------+
| bisdgboy777                      |
| dkittycc                         |
| tigaer                           |
| uhsksd                           |
| sysdney                          |
+----------------------------------+
5 rows in set (0.00 sec)

字符串长度

获取字符串的长度。其语法为：

length(str)

参数说明：

  • str：需要计算长度的字符串

例子：

mysql> select length('4794,3536,3565,7854');
+-------------------------------+
| length('4794,3536,3565,7854') |
+-------------------------------+
|                            19 |
+-------------------------------+
1 row in set (0.00 sec)

反转字符串

反转字符串，其语法为：

reverse(str)

例如：

mysql> select reverse('12345');
+------------------+
| reverse('12345') |
+------------------+
| 54321            |
+------------------+
1 row in set (0.00 sec)

字符串位置

返回子串substr在字符串 str中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；其语法为：

locate(substr, str)

例子：

mysql> select locate('2', '385295502');
+--------------------------+
| locate('2', '385295502') |
+--------------------------+
|                        4 |
+--------------------------+
1 row in set (0.00 sec)

窗口函数

窗口函数的作用：在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

  • 排名问题：每个部门按业绩来排名
  • topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数。

窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数语法：其中[]中的内容可以省略

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

简单来说，窗口函数有以下功能：

  1. 同时具有分组和排序的功能
  2. 不减少原表的行数

针对第二点，可以解释为啥有了group by和order by还需要用窗口函数。原因在于group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

sql server去除字符串中的符号 sql字符串去掉指定字符_sql server去除字符串中的符号_02

窗口函数大体可以分为以下两种：

  1. 能够作为窗口函数的聚合函数（sum，avg，count，max，min）
  2. rank，dense_rank，row_number等专用窗口函数。

下面简单介绍一下这些函数的基本使用方法。

专用窗口函数

专用窗口函数主要有rank，dense_rank，row_number。下面先来讲讲rank函数。

rank函数是用来计算记录排序的函数。

mysql> select * from user_profile;
+----+-----------+--------+------+--------------+------+-----------------------+--------------+------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 | question_cnt | answer_cnt |
+----+-----------+--------+------+--------------+------+-----------------------+--------------+------------+
|  1 |      2138 | male   |   21 | 北京大学     |  3.4 |                     7 |            2 |         12 |
|  2 |      3214 | male   | NULL | 复旦大学     |    4 |                    15 |            5 |         25 |
|  3 |      6543 | female |   20 | 北京大学     |  3.2 |                    12 |            3 |         30 |
|  4 |      2315 | female |   23 | 浙江大学     |  3.6 |                     5 |            1 |          2 |
|  5 |      5432 | male   |   25 | 山东大学     |  3.8 |                    20 |           15 |         70 |
|  6 |      2131 | male   |   28 | 山东大学     |  3.3 |                    15 |            7 |         13 |
|  7 |      4321 | male   |   28 | 复旦大学     |  3.6 |                     9 |            6 |         52 |
+----+-----------+--------+------+--------------+------+-----------------------+--------------+------------+
7 rows in set (0.00 sec)
mysql> select device_id, university, gpa, rank() over (partition by university order by gpa) as ranking from user_profile;
+-----------+--------------+------+---------+
| device_id | university   | gpa  | ranking |
+-----------+--------------+------+---------+
|      6543 | 北京大学     |  3.2 |       1 |
|      2138 | 北京大学     |  3.4 |       2 |
|      4321 | 复旦大学     |  3.6 |       1 |
|      3214 | 复旦大学     |    4 |       2 |
|      2131 | 山东大学     |  3.3 |       1 |
|      5432 | 山东大学     |  3.8 |       2 |
|      2315 | 浙江大学     |  3.6 |       1 |
+-----------+--------------+------+---------+
7 rows in set (0.00 sec)

参数解释：

  • partition by能够设定排序的对象范围，类似于group by语句，这里就是以university划分排序范围。
  • order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。

窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

要注意的是partition by不是窗口函数所必须的，比如：

mysql> select device_id, university, gpa, rank() over (order by gpa) as ranking from user_profile;
+-----------+--------------+------+---------+
| device_id | university   | gpa  | ranking |
+-----------+--------------+------+---------+
|      6543 | 北京大学     |  3.2 |       1 |
|      2131 | 山东大学     |  3.3 |       2 |
|      2138 | 北京大学     |  3.4 |       3 |
|      2315 | 浙江大学     |  3.6 |       4 |
|      4321 | 复旦大学     |  3.6 |       4 |
|      5432 | 山东大学     |  3.8 |       6 |
|      3214 | 复旦大学     |    4 |       7 |
+-----------+--------------+------+---------+
7 rows in set (0.00 sec)

如果省略了partition by，没有进行范围的划分，就会直接对全部的数据进行排序。

但如果这样用的话就失去了窗口函数的功能，所以一般不要这么使用。

专用窗口函数rank, dense_rank, row_number有什么区别呢？

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

得到如下结果：

sql server去除字符串中的符号 sql字符串去掉指定字符_字符串_03


从上面的结果可以看出：

  • rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
  • dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
  • row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

这三个函数的区别如下：

sql server去除字符串中的符号 sql字符串去掉指定字符_database_04

注意：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

聚合函数

聚和窗口函数和上面的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

还是用上面的数据，来看看下面的结果：

mysql> select device_id, university, gpa, sum(gpa) over (order by device_id) as sum_gpa, max(gpa) over (order by device_id) as max_gpa from user_profile;
+-----------+--------------+------+--------------------+--------------------+
| device_id | university   | gpa  | sum_gpa            | max_gpa            |
+-----------+--------------+------+--------------------+--------------------+
|      2131 | 山东大学     |  3.3 |  3.299999952316284 |  3.299999952316284 |
|      2138 | 北京大学     |  3.4 |  6.700000047683716 | 3.4000000953674316 |
|      2315 | 浙江大学     |  3.6 | 10.299999952316284 | 3.5999999046325684 |
|      3214 | 复旦大学     |    4 | 14.299999952316284 |                  4 |
|      4321 | 复旦大学     |  3.6 | 17.899999856948853 |                  4 |
|      5432 | 山东大学     |  3.8 | 21.699999809265137 |                  4 |
|      6543 | 北京大学     |  3.2 | 24.899999856948853 |                  4 |
+-----------+--------------+------+--------------------+--------------------+
7 rows in set (0.00 sec)

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如device_id为3214的记录，在使用sum窗口函数后的结果，是对2131、2138、2315和3214的gpa求和，若是6543，则结果是所有成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

进阶题目

字符串拆分（有分隔符）

数据库中num字段值为：'4794,3536,3565,7854'，现在需要将一行数据变成多行，也就是下面的样子：

+------+
| num  |
+------+
| 7654 |
| 7698 |
| 7782 |
| 7788 |
+------+
  1. 首先获取最后需被拆分成多少个字符串，利用 mysql 库的help_topic表的help_topic_id来模拟遍历第n个字符串，因为help_topic_id是自增的，当然也可以用其他表的自增字段辅助。help_topic_id的使用见下面：
mysql> select help_topic_id from mysql.help_topic where help_topic_id < 5;
+---------------+
| help_topic_id |
+---------------+
|             0 |
|             1 |
|             2 |
|             3 |
|             4 |
+---------------+
5 rows in set (0.00 sec)
  1. 通过如下的函数，求得,的个数：
mysql> select length('7654,7698,7782,7788') - length(replace('7654,7698,7782,7788', ',', '')) as num;
+------+
| num  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

也就是说，现在有三个逗号，那么就有四个字符串需要被分割。

  1. 根据逗号拆分字符串，此处利用substring_index()函数提取每部分的数字，最后把结果赋值给num字段。
select substring_index(substring_index('7654,7698,7782,7788', ',', help_topic_id + 1), ',', -1) as num;
  1. 将第三步的的help_topic_id限制在要提取的字符串个数内，也就是第二步，就可以得到这道题的最终结果了。
mysql> select substring_index(substring_index('7654,7698,7782,7788', ',', help_topic_id + 1), ',', -1) as num from mysql.help_topic where help_topic_id < length('7654,7698,7782,7788') - length(replace('7654,7698,7782,7788', ',', '')) + 1;
+------+
| num  |
+------+
| 7654 |
| 7698 |
| 7782 |
| 7788 |
+------+
4 rows in set (0.00 sec)

字符串拆分（无分隔符）

数据库中num字段值为：'123456'，现在需要将一行数据变成多行，也就是下面的样子：

+------+
| num  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
+------+

这道题和上一道题其实是一个思路，既然现在没有分隔符了，那就一个一个数的取，使用left配合substring函数就可以做到，如下：

mysql> select left(substring('123456', help_topic_id + 1), 1) as num from mysql.help_topic where help_topic_id < length('123456');
+------+
| num  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
+------+
6 rows in set (0.00 sec)

SQL32 截取出年龄

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

sql server去除字符串中的符号 sql字符串去掉指定字符_mysql_05

select
  substring_index(substring_index(profile, ',', -2), ',', 1) as age,
  count(*)
from
  user_submit
group by
  age;

SQL33 找出每个学校GPA最低的同学

现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

sql server去除字符串中的符号 sql字符串去掉指定字符_database_06

方法一：第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。

注意这样如果最低gpa对应多个同学，都会输出。

select
  a.device_id,
  a.university,
  a.gpa
from
  user_profile a
where
  gpa in (
    select
      min(b.gpa)
    from
      user_profile b
    where
      a.university = b.university
  )
order by
  a.university;

方法二：用内连接将单表分为两个表，再将两个表进行连接进行查询。

select
  a.device_id,
  a.university,
  a.gpa
from
  user_profile a
  join (
    select
      university,
      min(gpa) as gpa
    from
      user_profile
    group by
      university
  ) as u
  on a.university = u.university and a.gpa = u.gpa
  order by a.university;

方法三：借助all函数。

select
  a.device_id,
  a.university,
  a.gpa
from
  user_profile a
where
  gpa <= all(
    select
      b.gpa
    from
      user_profile b
    where
      a.university = b.university
  )
order by
  a.university;

方法四：窗口函数

select
  device_id,
  university,
  gpa
from
  (
    select
      *,
      row_number() over (partition by university order by gpa) as rn
    from
      user_profile
  ) as univ_min
where
  rn = 1
order by
  university;

SQL29 计算用户的平均次日留存率

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

sql server去除字符串中的符号 sql字符串去掉指定字符_字符串_07

方法一：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。

可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率。

select
  count(date2) / count(date1) as avg_ret
from
  (
    select
      distinct q.device_id,
      q.date as date1,
      p.date as date2
    from
      question_practice_detail as q
      left join(
        select
          distinct device_id,
          date
        from
          question_practice_detail
      ) as p 
      on q.device_id = p.device_id
      and date_add(q.date, interval 1 day) = p.date
  )as id_last_next_date;

方法二：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)

检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。

select
  avg(if(datediff(date2, date1) = 1, 1, 0)) as avg_ret
from
  (
    select
      distinct device_id,
      date as date1,
      lead(date) over (
        partition by device_id
        order by
          date
      ) as date2
    from
      (
        select
          distinct device_id,
          date
        from
          question_practice_detail
      ) as uniq_id_date
  ) as id_last_next_date;

参考