MySql（20）窗口函数

Mysql专栏入口

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码：ttst
提取码：ttst
专栏中有mysql的导入导出，里面有教如何导入

一、窗口函数使用

1.1 数据创建

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

1.2 序号函数

1.2.1 ROW_NUMBER()

1、2、3、4

# 查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。
SELECT
	ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
	id,category_id,category,NAME,price,stock 
FROM
	goods;

# 查询 goods 数据表中每个商品分类下价格最高的3种商品信息。
SELECT *
FROM (
	SELECT	ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
	row_num,
	id,	category_id, category, NAME, price, stock
	FROM goods) t
WHERE row_num <= 3;

1.2.2 RANK()

1、2、2、4

SELECT
	RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
	id,category_id,category,NAME,price,stock 
FROM
	goods;

1.2.3 DENSE_RANK()

1、2、2、3

# 使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,											
id, category_id, category, NAME, price, stock					
FROM	goods;