网站导航
> 文档中心 > SQL 题解记录 1-10

SQL 题解记录 1-10

文档中心 2022-05-29 19:16:58 39

文章目录

      • 一、分页查询employees表，每5行一页，返回第2页的数据——limit语句
      • 二、 将titles_test表名修改为titles_2017——rename语句
      • 三、最差是第几名——开窗函数
      • 四、获取employees中的first_name——截取函数
      • 五、查找字符串中逗号出现的次数——替换函数、长度函数
      • 六、将id=5以及emp_no=10001的行数据替换成emp_no=10005——替换函数
      • 七、出现相同积分三次以上的情况——group by语句、聚合函数
      • 八、将所有to_date为9999-01-01的全部更新为NUL——update语句

写在前面：
    记录一下解 sql 题的心路历程，题目来自牛客网 sql 专栏，按通过率由高到低刷起，题解来自评论区提供的思路，每 10 道题记录一篇博客，有问题的地方欢迎讨论。

一、分页查询employees表，每5行一页，返回第2页的数据——limit语句

原题目地址：分页查询employees表，每5行一页，返回第2页的数据

    语句 limit，第一个参数为开始记录数（从0开始），第二个参数是记录的条数，此题实际上是从第5条到第9条。

SELECT * FROM employeeslimit 5,5;

二、 将titles_test表名修改为titles_2017——rename语句

原题目地址：将titles_test表名修改为titles_2017
     修改表名，属于DDL操作，使用 rename to 语句即可：

alter table titles_test rename to titles_2017;

三、最差是第几名——开窗函数

原题目地址：最差是第几名(一)

    这道题我一开始想到的思路是按 grade 排序，然后当前成绩最差的名次数应该等于上一档次的最差名次+当前成绩人数，但是想不来怎么获得上一档次的最差名次（有点递归那意思😂，而且毕竟这是 sql，没有数组的存储方式），参考了下评论，主要是通过开窗函数。
    开窗函数格式： 函数名 (列) OVER (选项)，OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
比如:

sum (number) over(order by grade range between unbounded preceding and current row);

    这里的开窗函数sum (number) over (order by grade range between unbounded preceding and current row) 表示按照 grade 进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的和，还可以写作：sum (number) over (order by grade rows between unbounded preceding and current row) （也就是把 range 换成了 rows，前者是比较常见的定位框架）这样的计算结果就是按照 grade 进行排序的人数值的累积和。同理，如果是想要求 当前行前 2 行，到当前行后 2 行的累加和，可以写成：(order by grade rows between 2 preceding and 2 following)
    回到这道题，可以写成简化版的累加：

sum (number) over (order by grade);

    也表示从第一行累加到当前行。
    整个的 sql 语句为：

select grade,sum(number) over(order by grade) as t_rankfrom class_gradeorder by grade;

    关于开窗函数，比较详细的介绍可以参考这篇文章：https://www.cnblogs.com/lihaoyang/p/6756956.html

四、获取employees中的first_name——截取函数

原题目地址：获取employees中的first_name
     主要是 sql 截取字符串方法：

  • 从左到右截取，就用 left(field/string,length)；
  • 从右到左截取，就用 right(field/string,length)；
  • 指定位置开始截取，substring_index(str,s,count)；
  • 从字符第 count 次出现的位置开始截取字符串（count可为符负数，若找不到指定字符，则返回整个字符串）就用 substring(field/string,start,length)。

本题从右向左截取两位，作为排序依据即可：

select first_namefrom employeesorder by right(first_name,2) asc

五、查找字符串中逗号出现的次数——替换函数、长度函数

原题目地址：查找字符串中逗号出现的次数
   似乎没有封装好能直接用的函数，有 replace 替换函数、length 长度函数，可以用空替换逗号，然后用原字符串的长度，减去替换后的字符串的长度，再除以逗号的长度，也就是1，SQL如下：

select id,length(string)-length(replace(string,',','')) as cntfrom strings;

   注意：replace 方法返回的是替换后的结果，length 的入参，相当于一整个 field 传进来，暂且这么理解吧。

六、将id=5以及emp_no=10001的行数据替换成emp_no=10005——替换函数

原题目地址：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
   如果单纯用 replace 函数，那只能是：

replace(emp_no,10001,10005)

replace 函数返回值是替换后的结果，这里其实就是 10005，下一步只能是 update 了，题目只是说不能直接用 update，间接用是可以的：

update titles_testset emp_no = replace(emp_no,10001,10005)where id = 5;

    第二种解法是 replace into，有点像 insert，不过它是“有则改之，无则加”，类似于 insert into 的写法，原理应该是通过 id 判断的：

replace into titles_test values('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01')

七、出现相同积分三次以上的情况——group by语句、聚合函数

原题目地址：出现相同积分三次以上的情况
      查找出现三次及以上的积分，首先要根据积分进行分组，使用 group by 函数，然后用 count()即可知道每个分数出现的次数，求出次数大于等于3的即可，因为用到了聚合函数（sum(求和) 、count() 记录数 、max(最大值)、min(最小值)这些都叫聚合函数），所有条件不能用 where，而是 having：

select numberfrom gradegroup by numberhaving count(*)>=3

八、将所有to_date为9999-01-01的全部更新为NUL——update语句

原题目地址：将所有to_date为9999-01-01的全部更新为NULL
考察的应该是 update table set column value：

update titles_test set to_date=null,from_date='2001-01-01'where to_date='9999-01-01'​
标签：