MYSQL窗口函数

糖逗

参考链接：https://zhuanlan.zhihu.com/p/92654574

1. USE train;
   
2. 
   
3. #创建表
   
4. DROP TABLE IF EXISTS class;
   
5. CREATE TABLE class(
   
6.         学号 VARCHAR(255) PRIMARY KEY,
   
7.         班级 VARCHAR(255) NOT NULL,
   
8.         成绩 FLOAT(3) NOT NULL
   
9. );
   
10. 
    
11. #插入数据
    
12. INSERT INTO class
    
13. VALUES ('0001', '1', 86),
    
14.         ('0002', '1', 95),
    
15.         ('0003', '2', 89),
    
16.         ('0004', '1', 83),
    
17.         ('0005', '2', 86),
    
18.         ('0006', '3', 92),
    
19.         ('0007', '3', 86),
    
20.         ('0008', '1', 88);
    
21. 
    
22. #每个班级成绩排序
    
23. SELECT *,
    
24.         RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
    
25. FROM class
    
26. 
    
27. 
    
28. #直接按照成绩排序不考虑班级
    
29. SELECT *,
    
30.         RANK() OVER(ORDER BY 成绩 DESC) AS ranking,
    
31.         DENSE_RANK() OVER(ORDER BY 成绩 DESC) AS dense_ranking,
    
32.         ROW_NUMBER() OVER(ORDER BY 成绩 DESC) AS row_num
    
33. FROM class;
    
34. 
    
35. #聚合函数
    
36. SELECT *,
    
37.    SUM(成绩) OVER (ORDER BY 学号) AS current_sum,
    
38.    AVG(成绩) OVER (ORDER BY 学号) AS current_avg,
    
39.    COUNT(成绩) OVER (ORDER BY 学号) AS current_count,
    
40.    MAX(成绩) OVER (ORDER BY 学号) AS current_max,
    
41.    MIN(成绩) OVER (ORDER BY 学号) AS current_min
    
42. FROM class;
    
43. 
    
44. 
    

复制代码