MYSQL窗口函数
参考链接：https://zhuanlan.zhihu.com/p/92654574USE train;
#创建表
DROP TABLE IF EXISTS class;
CREATE TABLE class(
学号 VARCHAR(255) PRIMARY KEY,
班级 VARCHAR(255) NOT NULL,
成绩 FLOAT(3) NOT NULL
);
#插入数据
INSERT INTO class
VALUES ('0001', '1', 86),
('0002', '1', 95),
('0003', '2', 89),
('0004', '1', 83),
('0005', '2', 86),
('0006', '3', 92),
('0007', '3', 86),
('0008', '1', 88);
#每个班级成绩排序
SELECT *,
RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class
#直接按照成绩排序不考虑班级
SELECT *,
RANK() OVER(ORDER BY 成绩 DESC) AS ranking,
DENSE_RANK() OVER(ORDER BY 成绩 DESC) AS dense_ranking,
ROW_NUMBER() OVER(ORDER BY 成绩 DESC) AS row_num
FROM class;
#聚合函数
SELECT *,
SUM(成绩) OVER (ORDER BY 学号) AS current_sum,
AVG(成绩) OVER (ORDER BY 学号) AS current_avg,
COUNT(成绩) OVER (ORDER BY 学号) AS current_count,
MAX(成绩) OVER (ORDER BY 学号) AS current_max,
MIN(成绩) OVER (ORDER BY 学号) AS current_min
FROM class;
页:
[1]