MySQL进阶二

目录

1.使用环境

2.排序窗口函数

3.聚合窗口函数

---

1.使用环境

数据库：MySQL 8.0.30

客户端：Navicat 15.0.12

接续MySQL进阶一：

MySQL进阶一-CSDN博客文章浏览阅读452次，点赞9次，收藏4次。MySQL进阶操作一。https://blog.csdn.net/ak2111/article/details/137614544?spm=1001.2014.3001.5502

进阶一和进阶二合并：

MySQL进阶-合-CSDN博客文章浏览阅读665次，点赞8次，收藏10次。进阶一和进阶二的合并版本。https://blog.csdn.net/ak2111/article/details/137716476?spm=1001.2014.3001.5502 

2.排序窗口函数

先建表：

CREATE TABLE student0413 (
  sid varchar(255),
  gender varchar(255) DEFAULT NULL,
  sname varchar(255) DEFAULT NULL,
  caption varchar(255) DEFAULT NULL,
  cname varchar(255) DEFAULT NULL,
  tname varchar(255) DEFAULT NULL,
  num varchar(255) DEFAULT NULL
)

> OK
> 时间: 0.01s

添加数据：

INSERT INTO student0413 VALUES 
('1', '男', '李一一', '三年二班', '生物', '张老师', '11'),
('1', '男', '李一一', '三年二班', '物理', '李老师', '8'),
('1', '男', '李一一', '三年二班', '美术', '李老师', '67'),
('2', '女', '王一二', '三年二班', '生物', '张老师', '9'),
('2', '女', '王一二', '三年二班', '体育', '刘老师', '69'),
('2', '女', '王一二', '三年二班', '美术', '李老师', '98'),
('3', '男', '张三', '三年二班', '生物', '张老师', '78'),
('3', '男', '张三', '三年二班', '物理', '李老师', '67'),
('3', '男', '张三', '三年二班', '体育', '刘老师', '88'),
('3', '男', '张三', '三年二班', '美术', '李老师', '98'),
('4', '男', '张一', '三年二班', '生物', '张老师', '78'),
('4', '男', '张一', '三年二班', '物理', '李老师', '12'),
('4', '男', '张一', '三年二班', '体育', '刘老师', '68'),
('4', '男', '张一', '三年二班', '美术', '李老师', '100'),
('5', '女', '张二', '三年二班', '生物', '张老师', '78'),
('5', '女', '张二', '三年二班', '物理', '李老师', '12'),
('5', '女', '张二', '三年二班', '体育', '刘老师', '68'),
('5', '女', '张二', '三年二班', '美术', '李老师', '100'),
('6', '男', '张四', '三年二班', '生物', '张老师', '8'),
('6', '男', '张四', '三年二班', '物理', '李老师', '100'),
('6', '男', '张四', '三年二班', '体育', '刘老师', '68'),
('6', '男', '张四', '三年二班', '美术', '李老师', '100'),
('7', '女', '丁一', '三年三班', '生物', '张老师', '8'),
('7', '女', '丁一', '三年三班', '物理', '李老师', '100'),
('7', '女', '丁一', '三年三班', '体育', '刘老师', '68'),
('7', '女', '丁一', '三年三班', '美术', '李老师', '89'),
('8', '男', '李三', '三年三班', '生物', '张老师', '8'),
('8', '男', '李三', '三年三班', '物理', '李老师', '100'),
('8', '男', '李三', '三年三班', '体育', '刘老师', '68'),
('8', '男', '李三', '三年三班', '美术', '李老师', '89'),
('9', '男', '李一', '三年三班', '生物', '张老师', '92'),
('9', '男', '李一', '三年三班', '物理', '李老师', '89'),
('9', '男', '李一', '三年三班', '体育', '刘老师', '68'),
('9', '男', '李一', '三年三班', '美术', '李老师', '23'),
('10', '女', '李二', '三年三班', '生物', '张老师', '91'),
('10', '女', '李二', '三年三班', '物理', '李老师', '78'),
('10', '女', '李二', '三年三班', '体育', '刘老师', '44'),
('10', '女', '李二', '三年三班', '美术', '李老师', '88'),
('11', '男', '李四', '三年三班', '生物', '张老师', '91'),
('11', '男', '李四', '三年三班', '物理', '李老师', '78'),
('11', '男', '李四', '三年三班', '体育', '刘老师', '44'),
('11', '男', '李四', '三年三班', '美术', '李老师', '88'),
('12', '女', '赵五', '一年二班', '生物', '张老师', '91'),
('12', '女', '赵五', '一年二班', '物理', '李老师', '78'),
('12', '女', '赵五', '一年二班', '体育', '刘老师', '44'),
('12', '女', '赵五', '一年二班', '美术', '李老师', '88'),
('13', '男', '刘一五', '一年二班', '体育', '刘老师', '88');

> Affected rows: 47
> 时间: 0.002s

查询每个班分数最高的学生的分数：

select caption,max(num) as 最高分 from student0413 group by caption;

 把每个班的学生成绩从高到低进行排序，并将排名结果作为新的一列和原数据汇总到一起：

select *,rank() over(partition by caption order by num desc) as 排名 from student0413;

使用dense_rank排名函数，查看和rank的区别：

select *,dense_rank() over(partition by caption order by num desc) as 排名
from student;

使用row_number排名函数，查看和rank和dense_rank的区别：

select *,row_number() over(partition by caption order by num desc) as 排名 from student0413;

查询每个班的分数排名前2的学生信息：

select * from
(select *, dense_rank() over(partition by caption order by num desc) 排名 from student0413) as e
where e.排名 <= 2;

查找每个班级成绩最高的所有学员信息： 

select * from 
(select *, dense_rank() over(partition by caption order by num desc) 排名 from student0413) as e
where 排名 <= 1;

3.聚合窗口函数

 用分组聚合实现每个学生及格科目的数量统计：

select sname, count(cname) 及格学科数量 from 
(select * from student0413 where num >= 60) as e 
group by sname;

用窗口函数实现每个学生及格科目的数量统计：

select sname, count(cname) over(partition by sname) 及格学科数量 
from student0413 where num >= 60;

 查询每个月的总销量与总销售额：

建表：

create table if not exists sale_order(
id int primary key auto_increment, -- 订单id
sale date, -- 订单时间
user_id int, -- 用户id
pro_id int, -- 商品类型id 
sale_count int, -- 销售数量
price int, -- 销售单价
amount int -- 销售金额
);

> OK
> 时间: 0.008s

添加数据：

insert into sale_order(sale, user_id, pro_id, sale_count, price, amount) values
('2024-01-01', 1, 101, 2, 150, 300),
('2024-01-02', 2, 101, 1, 100, 100),
('2024-02-10', 3, 101, 2, 90, 180),
('2024-02-11', 2, 102, 2, 200, 400),
('2024-03-01', 3, 102, 1, 100, 100),
('2024-03-01', 3, 101, 1, 60, 60),
('2024-03-01', 3, 103, 4, 120, 480);

> Affected rows: 7
> 时间: 0.001s

查询每个月的总销量与总销售额：

select date_format(sale, '%Y-%m') 月, sum(sale_count) 总销量, sum(amount) 总销售额
from sale_order group by 月;