SQL一些不熟悉函数总结

最新推荐文章于 2024-03-07 18:35:42 发布

Echoblabla

最新推荐文章于 2024-03-07 18:35:42 发布

阅读量303

点赞数

分类专栏： SQL 数据库文章标签： sql

本文链接：https://blog.youkuaiyun.com/Echoblabla/article/details/109967548

版权

SQL 同时被 2 个专栏收录

13 篇文章

订阅专栏

数据库

13 篇文章

订阅专栏

本文总结了SQL中的分组合并函数GROUP_CONCAT，逻辑函数如IFNULL和IF，以及开窗函数的概念和应用。通过案例展示了如何使用这些函数，包括静态窗口函数如row_number、rank等，以及动态窗口函数如first_value。同时，对比了开窗函数与普通聚合函数的区别，强调了开窗函数在每条记录上执行的特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分组合并函数

GROUP_CANCAT([distinct] str [order by str asc/desc] [separator])
将group by产生的同一个分组中的值连接起来，返回一个字符串结果
示例：查询每个部门的员工姓名

mysql> select deptno,group_concat(ename)
 -> from emp
 -> group by deptno;
+--------+--------------------------------------+
| deptno | group_concat(ename)         |
+--------+--------------------------------------+
|   10 | clark,king,miller          |
|   20 | smith,jones,scott,adams,ford     |
|   30 | allen,ward,martin,blake,turner,james |
+--------+--------------------------------------+

逻辑函数

IFNULL(expression, alt_value)
判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个
参数的值。

mysql> select ifnull(comm,0)
 -> from emp;
+----------------+
| ifnull(comm,0) |
+----------------+
|        0 |
|       300 |
|       500 |
|        0 |
|      1400 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
+----------------+

IF(expr1,expr2,expr3)
如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

mysql> select ename,sal,if(sal>=3000,'高',if(sal>=1500,'中','低')) 工资级别
 -> from emp;
+--------+------+--------------+
| ename | sal | 工资级别   |
+--------+------+--------------+
| smith |  800 | 低      |
| allen | 1600 | 中      |
| ward  | 1250 | 低      |
| jones | 2975 | 中      |
| martin | 1250 | 低      |
| blake | 2850 | 中      |
| clark | 2450 | 中      |
| scott | 3000 | 高      |
| king  | 5000 | 高      |
| turner | 1500 | 中      |
| adams | 1100 | 低      |
| james |  950 | 低      |
| ford  | 3000 | 高      |
| miller | 1300 | 低      |
+--------+------+--------------+

SQL中的IF ELSE语句
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END
如果expr1的值为true，则返回expr2的值，如果expr3的值为false，则返回expr4的值…

mysql> select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中'
else '低' end 工资级别
 -> from emp;
+--------+------+--------------+
| ename | sal | 工资级别   |
+--------+------+--------------+
| smith |  800 | 低      |
| allen | 1600 | 中      |
| ward  | 1250 | 低      |
| jones | 2975 | 中      |
| martin | 1250 | 低      |
| blake | 2850 | 中      |
| clark | 2450 | 中      |
| scott | 3000 | 高      |
| king  | 5000 | 高      |
| turner | 1500 | 中      |
| adams | 1100 | 低      |
| james |  950 | 低      |
| ford  | 3000 | 高      |
| miller | 1300 | 低      |
+--------+------+--------------+

开窗函数

开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有
的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着
不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，
它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<
滑动窗口>])

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围，
如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计
算；如果不为空，则有三个参数来设置窗口：

partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别
执行，在跨越分区边界时重新初始化。
order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和 partition
by子句配合使用，也可以单独使用。
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
静态窗口函数
row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() /
lead()/ntile()

拓展：动态窗口函数
动态窗口函数：first_value() / last_value()/nth_value()/聚合函数用于开窗
如没有指定排序和滑动窗口范围，默认计算的是分区内的所有记录。
指定分区和排序后，如没有指定滑动窗口范围，默认计算的是分区内的第一行到当前行。
对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，
frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

rows between 1 preceding and 1 following 窗口范围是分区中的当前行、前一行、后一行一共三
行记录。
rows between 1 preceding and current row 窗口范围是分区中的前一行、当前行一共两行记录。
rows between current row and 1 following 窗口范围是分区中的当前行、后一行一共两行记录。
rows unbounded preceding 窗口范围是分区中的第一行到当前行。
rows between unbounded preceding and current row 窗口范围是分区中的第一行到当前行。
rows between current row and unbounded following 窗口范围是分区中的当前行到最后一行。
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行。

开窗函数和普通聚合函数的区别：

聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几
条。
聚合函数也可以用于开窗函数中。
举个例子：
查询所有员工的平均工资
用开窗函数

mysql> select avg(sal) 所有员工平均工资 from emp;
+------------------+
| 所有员工平均工资 |
+------------------+
|     2073.2143 |
+------------------+
mysql> select *,avg(sal) over() 所有员工平均工资 from emp;
+-------+--------+-----------+------+------------+------+------+--------+-------
-----------+
| empno | ename | job    | mgr | hiredate  | sal | comm | deptno | 所有员工平均工资 |
+-------+--------+-----------+------+------------+------+------+--------+-------
-----------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800 | NULL |   20 |   2073.2143 |
|  7499 | allen | salesman | 7698 | 1981-02-20 | 1600|300| 30 |   2073.2143 |
|  7521 | ward  | salesman | 7698 | 1981-02-22 | 1250|500|   30 |   2073.2143 |
|  7566 | jones | manager  | 7839 | 1981-04-02 | 2975|NULL |   20 |   2073.2143 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250|1400 |   30 |   2073.2143 |
|  7698 | blake | manager  | 7839 | 1981-05-01 | 2850 | NULL |   30 |   2073.2143 |
|  7782 | clark | manager  | 7839 | 1981-06-09 | 2450 | NULL |   10 |   2073.2143 |
|  7788 | scott | analyst  | 7566 | 1987-04-19 | 3000 | NULL |   20 |   2073.2143 |
|  7839 | king  | persident | NULL | 1981-11-17 | 5000 | NULL |   10 |   2073.2143 |
|  7844 | turner | salesman | 7698 | 1981-09-08 | 1500 |   0 |   30 |   2073.2143 
 |7876 | adams | clerk   | 7788 | 1987-05-23 | 1100 | NULL |   20 |   2073.2143 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950 | NULL |   30 |   2073.2143 |
|  7902 | ford  | analyst  | 7566 | 1981-12-03 | 3000 | NULL |   20 |   2073.2143 |
|  7934 | miller | clerk   | 7782 | 1982-01-23 | 1300 | NULL |   10 |   2073.2143 |
+-------+--------+-----------+------+------------+------+------+--------+-------
-----------+

用聚合函数

mysql> select deptno,avg(sal) 部门平均工资 from emp group by deptno;
+--------+--------------+
| deptno | 部门平均工资 |
+--------+--------------+
|   10 |   2916.6667 |
|   20 |   2175.0000 |
|   30 |   1566.6667 |
+--------+--------------+
mysql> select *,avg(sal) over(partition by deptno) 部门平均工资 from emp;
+-------+--------+-----------+------+------------+------+------+--------+-------
-------+
| empno | ename | job    | mgr | hiredate  | sal | comm | deptno | 部门平
均工资 |
+-------+--------+-----------+------+------------+------+------+--------+-------
-------+
|  7782 | clark | manager  | 7839 | 1981-06-09 | 2450 | NULL |   10 | 
 2916.6667 |
|  7839 | king  | persident | NULL | 1981-11-17 | 5000 | NULL |   10 | 
 2916.6667 |
|  7934 | miller | clerk   | 7782 | 1982-01-23 | 1300 | NULL |   10 | 
 2916.6667 |
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800 | NULL |   20 | 
 2175.0000 |
|  7566 | jones | manager  | 7839 | 1981-04-02 | 2975 | NULL |   20 | 
 2175.0000 |
|  7788 | scott | analyst  | 7566 | 1987-04-19 | 3000 | NULL |   20 | 
 2175.0000 |
示例3：
|  7876 | adams | clerk   | 7788 | 1987-05-23 | 1100 | NULL |   20 | 
 2175.0000 |
|  7902 | ford  | analyst  | 7566 | 1981-12-03 | 3000 | NULL |   20 | 
 2175.0000 |
|  7499 | allen | salesman | 7698 | 1981-02-20 | 1600 |  300 |   30 | 
 1566.6667 |
|  7521 | ward  | salesman | 7698 | 1981-02-22 | 1250 |  500 |   30 | 
 1566.6667 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 |   30 | 
 1566.6667 |
|  7698 | blake | manager  | 7839 | 1981-05-01 | 2850 | NULL |   30 | 
 1566.6667 |
|  7844 | turner | salesman | 7698 | 1981-09-08 | 1500 |   0 |   30 | 
 1566.6667 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950 | NULL |   30 | 
 1566.6667 |
+-------+--------+-----------+------+------------+------+------+--------+-------
-------+

序号函数
row_number()
显示分区中不重复不间断的序号
dense_rank()
显示分区中重复不间断的序号
rank()
显示分区中重复间断的序号
示例：查询员工信息并按照工资高低显示排名

mysql> select *,row_number() over(order by sal desc) 排名 from emp;
示例2：查询员工信息并按照各部门的工资高低显示排名
+-------+--------+-----------+------+------------+------+------+--------+------+
| empno | ename | job    | mgr | hiredate  | sal | comm | deptno | 排名 |
+-------+--------+-----------+------+------------+------+------+--------+------+
|  7839 | king  | persident | NULL | 1981-11-17 | 5000 | NULL |   10 |   1 |
|  7788 | scott | analyst  | 7566 | 1987-04-19 | 3000 | NULL |   20 |   2 |
|  7902 | ford  | analyst  | 7566 | 1981-12-03 | 3000 | NULL |   20 |   3 |
|  7566 | jones | manager  | 7839 | 1981-04-02 | 2975 | NULL |   20 |   4 |
|  7698 | blake | manager  | 7839 | 1981-05-01 | 2850 | NULL |   30 |   5 |
|  7782 | clark | manager  | 7839 | 1981-06-09 | 2450 | NULL |   10 |   6 |
|  7499 | allen | salesman | 7698 | 1981-02-20 | 1600 |  300 |   30 |   7 |
|  7844 | turner | salesman | 7698 | 1981-09-08 | 1500 |   0 |   30 |   8 |
|  7934 | miller | clerk   | 7782 | 1982-01-23 | 1300 | NULL |   10 |   9 |
|  7521 | ward  | salesman | 7698 | 1981-02-22 | 1250 |  500 |   30 |  10 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 |   30 |  11 |
|  7876 | adams | clerk   | 7788 | 1987-05-23 | 1100 | NULL |   20 |  12 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950 | NULL |   30 |  13 |
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800 | NULL |   20 |  14 |
+-------+--------+-----------+------+------------+------+------+--------+------+

查询员工信息并按照各部门的工资高低显示排名

select *,
   row_number() over(partition by deptno order by sal desc) 排名1,
   dense_rank() over(partition by deptno order by sal desc) 排名2,
   rank() over(partition by deptno order by sal desc) 排名3
from emp;
+-------+--------+-----------+------+------------+------+------+--------+-------
+-------+-------+
| empno | ename | job    | mgr | hiredate  | sal | comm | deptno | 排名1 |
排名2 | 排名3 |
+-------+--------+-----------+------+------------+------+------+--------+-------
+-------+-------+
|  7839 | king  | persident | NULL | 1981-11-17 | 5000 | NULL |   10 |   1|   1 |   1 |
|  7782 | clark | manager  | 7839 | 1981-06-09 |2450|NULL|   10 |   2|   2 |   2 |
|  7934 | miller | clerk   | 7782 | 1982-01-23 | 1300 | NULL |   10 |   3|   3 |   3 |
|  7788 | scott | analyst  | 7566 | 1987-04-19 | 3000 | NULL |   20 |   1|   1 |   1 |
|  7902 | ford  | analyst  | 7566 | 1981-12-03 | 3000 | NULL |   20 |   2|   1 |   1 |
|  7566 | jones | manager  | 7839 | 1981-04-02 | 2975 | NULL |   20 |   3|   2 |   3 |
|  7876 | adams | clerk   | 7788 | 1987-05-23 | 1100 | NULL |   20 |   4|   3 |   4 |
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800 | NULL |   20 |   5|   4 |   5 |
|  7698 | blake | manager  | 7839 | 1981-05-01 | 2850 | NULL |   30 |   1|   1 |   1 |
|  7499 | allen | salesman | 7698 | 1981-02-20 | 1600 |  300 |   30 |   2|   2 |   2 |
|  7844 | turner | salesman | 7698 | 1981-09-08 | 1500 |   0 |   30 |   3|   3 |   3 |
|  7521 | ward  | salesman | 7698 | 1981-02-22 | 1250 |  500 |   30 |   4|   4 |   4 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 |   30 |   5|   4 |   4 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950 | NULL |   30 |   6|   5 |   6 |
+-------+--------+-----------+------+------------+------+------+--------+-------
+-------+-------+