常德网站建设wynet123,上海稼禾建设装饰集团网站,商丘简淘网络科技有限公司,wordpress 如何建站窗口函数的基本语法 窗口函数 OVER ([PARTITION BY 用于分组的列] ORDER BY 用于排序的列) ※ []中的内容可以省略 注意:Mysql从8.0版本才开始支持窗口函数 ROW_NUMBER()、RANK()、DENSE_RANK()的区别
(1) ROW_NUMBER(): 依次排序#xff0c;不会出… 窗口函数的基本语法 窗口函数 OVER ([PARTITION BY 用于分组的列] ORDER BY 用于排序的列) ※ []中的内容可以省略 注意:Mysql从8.0版本才开始支持窗口函数 ROW_NUMBER()、RANK()、DENSE_RANK()的区别
(1) ROW_NUMBER(): 依次排序不会出现相同排名
(2) RANK(): 出现相同排名时跳跃排序
(3) DENSE_RANK(): 出现相同排名时连续排序 导入数据
DROP TABLE IF EXISTS order_content;
CREATE TABLE order_content(
order_id VARCHAR(8),
user_id VARCHAR(8),
order_price INT
)
ENGINE InnoDB
DEFAULT CHARSET utf8;
INSERT INTO
order_content (order_id,user_id,order_price)
VALUE (o001,u001,800)
,(o002,u001,800)
,(o003,u001,1000)
,(o004,u001,1200)
,(o005,u002,400)
,(o006,u002,1500)
,(o007,u002,2100)
,(o008,u003,900)
,(o009,u003,700)
,(o010,u003,1700);
order_content表(订单内容表) order_id:订单号 user_id:下单用户 order_price:订单金额
使用RANK()函数
SELECT *,RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS ranking
FROM order_content;
结果展示: 以user_id字段分组并在组内按照order_price字段升序排列;在每组内按照order_price字段排名order_price字段值越小则排名越靠前。在使用RANK()函数排名时当要排名的数据值大小相同时它们的排名是一致的。例如o001和o002都是第一名而之后的排名从第3名开始如o003
使用DENSE_RANK()函数
SELECT *,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS dense_ranking
FROM order_content;
结果展示: 使用ROW_NUMBER()函数
SELECT *,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price) AS row_num
FROM order_content;
结果展示: 删除上述代码中的PARTITION BY user_id则结果和普通的ORDER BY结果一样
-- RANK()函数
SELECT *,RANK() OVER (ORDER BY order_price) AS ranking
FROM order_content;-- DENSE_RANK()函数
SELECT *,DENSE_RANK() OVER (ORDER BY order_price) AS dense_ranking
FROM order_content;-- ROW_NUMBER()函数
SELECT *,ROW_NUMBER() OVER (ORDER BY order_price) AS row_num
FROM order_content;
结果展示:
RANK()函数 DENSE_RANK()函数 ROW_NUMBER()函数 查询每个用户订单金额最高的订单信息
-- 第一种解法
SELECT *
FROM (SELECT *,RANK() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS ranking FROM order_content)AS a
WHERE a.ranking 1;-- 第二种解法
SELECT *
FROM (SELECT *,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS dense_rankingFROM order_content)AS a
WHERE a.dense_ranking 1;-- 第三种解法
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS row_numFROM order_content)AS a
WHERE a.row_num 1;
结果展示: 在子查询内部使用RANK()函数 / DENSE_RANK()函数 / ROW_NUMBER()函数通过子查询得到子查询内部结果在子查询外部筛选使用RANK()函数 / DENSE_RANK()函数 / ROW_NUMBER()函数所得结果中的第一名得到每个用户订单金额最高的订单信息结果
RANK()、DENSE_RANK()、ROW_NUMBER()函数的异同
SELECT *,RANK() OVER (PARTITION BY user_id ORDER BY order_price)AS ranking,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price)AS dense_ranking,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price)AS row_num
FROM order_content;
结果展示: 从上面红色框中的结果可以看出:
rank函数
这个例子中是1位1位3位4位也就是如果有并列名次的行会占用下一名次的位置
比如正常排名是1234但是现在前3名是并列的名次结果是1114
dense_rank函数
这个例子中是1位1位2位3位也就是如果有并列名次的行不占用下一名次的位置
比如正常排名是1234但是现在前3名是并列的名次结果是1112
row_number函数
这个例子中是1位2位3位4位也就是不考虑并列名次的情况
比如前3名是并列的名次排名是正常的1234 练习案例
案例1
导入数据
DROP TABLE IF EXISTS SC;
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
)
ENGINE InnoDB
DEFAULT CHARSET utf8; insert into SC values(01 , 01 , 80);
insert into SC values(01 , 02 , 90);
insert into SC values(01 , 03 , 99);
insert into SC values(02 , 01 , 70);
insert into SC values(02 , 02 , 60);
insert into SC values(02 , 03 , 80);
insert into SC values(03 , 01 , 80);
insert into SC values(03 , 02 , 80);
insert into SC values(03 , 03 , 80);
insert into SC values(04 , 01 , 50);
insert into SC values(04 , 02 , 30);
insert into SC values(04 , 03 , 20);
insert into SC values(05 , 01 , 76);
insert into SC values(05 , 02 , 87);
insert into SC values(06 , 01 , 31);
insert into SC values(06 , 03 , 34);
insert into SC values(07 , 02 , 89);
insert into SC values(07 , 03 , 98); sc表 1.1按各科成绩进行排序并显示排名Score重复时也继续排名
SELECT *,ROW_NUMBER() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示: 1.2按各科成绩进行排序并显示排名Score重复时合并名次
使用窗口函数rank()
SELECT *,RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示: 使用窗口函数dense_rank()
SELECT *,DENSE_RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示: 案例2:获取每个部门中当前员工薪水最高的相关信息
导入数据
drop table if exists dept_emp ;
drop table if exists salaries ;
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO dept_emp VALUES(10001,d001,1986-06-26,9999-01-01);
INSERT INTO dept_emp VALUES(10002,d001,1996-08-03,9999-01-01);
INSERT INTO dept_emp VALUES(10003,d002,1996-08-03,9999-01-01);INSERT INTO salaries VALUES(10001,88958,2002-06-22,9999-01-01);
INSERT INTO salaries VALUES(10002,72527,2001-08-02,9999-01-01);
INSERT INTO salaries VALUES(10003,92527,2001-08-02,9999-01-01);
dept_emp表(员工表) salaries表(薪水表) 问题:获取每个部门中当前员工薪水最高的相关信息给出dept_no, emp_no以及其对应的salary按照部门编号dept_no升序排列
SELECT temp.dept_no, temp.emp_no, temp.salary AS maxSalary
FROM (SELECT a.dept_no, b.emp_no, b.salary,DENSE_RANK() OVER (PARTITION BY a.dept_no ORDER BY b.salary DESC)AS dense_rankingFROM dept_emp AS aINNER JOIN salaries AS bON a.emp_no b.emp_no)AS temp
WHERE temp.dense_ranking 1
ORDER BY temp.dept_no ASC;
结果展示: 案例3:获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
导入数据
drop table if exists salaries ;
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO salaries VALUES(10001,88958,2002-06-22,9999-01-01);
INSERT INTO salaries VALUES(10002,72527,2001-08-02,9999-01-01);
INSERT INTO salaries VALUES(10003,43311,2001-12-01,9999-01-01);salaries表 问题:获取薪水第二多的员工的emp_no以及其对应的薪水salary若有多个员工的薪水为第二多的薪水则将对应的员工的emp_no和salary全部输出并按emp_no升序排序
SELECT a.emp_no, a.salary
FROM(SELECT emp_no, salary,DENSE_RANK() OVER(ORDER BY salary DESC)AS dense_rankingFROM salaries)AS a
WHERE a.dense_ranking 2
ORDER BY a.emp_no ASC;
结果展示:
