最全MySQL8.0实战教程 12 MySQL的函数 12.7 窗口函数【4 序号函数】

分手后的思念是犯贱 2024-03-26 13:23 55阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  12 MySQL的函数
         *   *  12.7 窗口函数
             *   *  12.7.4 序号函数

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 12 MySQL的函数 ####

##### 12.7 窗口函数 #####

###### 12.7.4 序号函数 ######

序号函数有三个：ROW\_NUMBER()、RANK()、DENSE\_RANK()，可以用来实现分组排序，并添加序号。

【格式】

row_number()|rank()|dense_rank() over ( 
      partition by ... 
      order by ... 
    )

【操作】

use mydb4; 
    create table employee( 
       dname varchar(20), -- 部门名 
       eid varchar(20), 
       ename varchar(20), 
       hiredate date, -- 入职日期 
       salary double -- 薪资
    ); 
    
    insert into employee values('研发部','1001','刘备','2021-11-01',3000);
    insert into employee values('研发部','1002','关羽','2021-11-02',5000);
    insert into employee values('研发部','1003','张飞','2021-11-03',7000);
    insert into employee values('研发部','1004','赵云','2021-11-04',7000);
    insert into employee values('研发部','1005','马超','2021-11-05',4000);
    insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
    insert into employee values('销售部','1007','曹操','2021-11-01',2000);
    insert into employee values('销售部','1008','许褚','2021-11-02',3000);
    insert into employee values('销售部','1009','典韦','2021-11-03',5000);
    insert into employee values('销售部','1010','张辽','2021-11-04',6000);
    insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
    insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

![在这里插入图片描述][0cc9acf204d04fbead881326faf4f627.png_pic_center]

-- 对每个部门的员工按照薪资排序，并给出排名
    select 
    	dname,
    	ename,
    	salary,
    	row_number() over(
    	partition by dname -- 按照部门分组
    	order by salary desc -- 按照薪资排序
    	) as rn 
    from employee;

![在这里插入图片描述][9cd69d6f77d84dfc86ff97a9f8f815d9.png_pic_center]

-- 对每个部门的员工按照薪资排序，并给出排名 rank
    select 
    	dname,
    	ename,
    	salary,
    	rank() over(
    		partition by dname 
    		order by salary desc
    	) as rn 
    from employee;

![在这里插入图片描述][bef6a3fe56f6479690fb361cb9e50d23.png_pic_center]

这种方式可以并列

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
    select 
    	dname,
    	ename,
    	salary,
    	dense_rank() over(
    		partition by dname 
    		order by salary desc
    	) as rn 
    from employee;

![在这里插入图片描述][14a079886e5e43bdacf3341c1ebfb074.png_pic_center]

排名尽管并列，但是连续

-- 求出每个部门薪资排在前三名的员工- 分组求TOPN
    select 
    * 
    from 
    (
        select 
         dname,
         ename,
         salary,
         dense_rank() over(partition by dname order by salary desc)  as rn
        from employee
    ) as t
    where t.rn <= 3;

![在这里插入图片描述][e2e81bc4e31b4fbf87c8ab0be9b7e945.png_pic_center]

-- 对所有员工进行全局排序（不分组）
    -- 不加partition by表示全局排序
    select 
         dname,
         ename,
         salary,
         dense_rank() over(
    			order by salary desc
    		 )  as rn
    from employee;

![在这里插入图片描述][a5dc6f2364fc489ea6d1e69ec6fe868c.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[0cc9acf204d04fbead881326faf4f627.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/41f4ff5d9b9c49a6b189bccf242d7092.png
[9cd69d6f77d84dfc86ff97a9f8f815d9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8a233bd444cd49aeae512a5491f9841f.png
[bef6a3fe56f6479690fb361cb9e50d23.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/87afbfb92c624221ba4560de1c4be090.png
[14a079886e5e43bdacf3341c1ebfb074.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/293b4d7c37434aed8e7b33dc45a0cd19.png
[e2e81bc4e31b4fbf87c8ab0be9b7e945.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a3486181329a4ef6b5cdad1b9c2af2c1.png
[a5dc6f2364fc489ea6d1e69ec6fe868c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/44d8f1094a93410393bf7e92deb0d0a0.png