第一题

1.jpg

第一题：
用一条sql语句，查询出每门课程都大于80分的学生姓名

建表语句：

1
2
3
4
5
6
7
8
9
10
drop table if exists t_student;
create table t_student(
	name varchar(255),
	kecheng varchar(255),
	fenshu double(3,1)
);
insert into t_student values('张三', '语文', 81);
insert into t_student values('张三', '数学', 75);
insert into t_student values('王五', '英语', 90);
select * from t_student;

思路：查询出小于80分的所欲学生姓名（去重），然后使用not in，不去重会有多个少于80分的重复名字

1
2
3
4
5
# 第一步：找小于等于80分的学员姓名
select distinct name from t_student where fenshu <= 80

# 第二步：not in
select distinct name from t_student where name not in(select distinct name from t_student where fenshu <= 80)

第二题

2.jpg
image.png
其中，两个表的关联字段为申请单号。
1）查询身份证号为440401430103082的申请日期。
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。
3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。
4）删除g_cardapplydetail表中所有姓李的记录。
模拟数据：考试做这种题目最重要的是要冷静下来，只有静下来SQL才能写好。要模拟数据。看到数据SQL就好写了。
image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
drop table if exists g_cardapply;
create table g_cardapply(
	g_applyno varchar(8) primary key,
	g_applydate varchar(255),
	g_state varchar(2)
);

insert into g_cardapply values(1,'2008-08-08', '01');
insert into g_cardapply values(2,'2022-10-11', '01');
insert into g_cardapply values(3,'2023-03-23', '01');
insert into g_cardapply values(4,'2007-12-12', '02');
insert into g_cardapply values(5,'2009-12-11', '02');
select * from g_cardapply;

drop table if exists g_cardapplydetail;
create table g_cardapplydetail(
	g_applyno varchar(8),
	g_name varchar(8),
	g_idcard varchar(30),
	g_state varchar(2)
);
insert into g_cardapplydetail values('1','张三','440401430103082','01');
insert into g_cardapplydetail values('2','张三','440401430103082','01');
insert into g_cardapplydetail values('3','张三','440401430103082','01');
insert into g_cardapplydetail values('4','李四','440401430111111','02');
insert into g_cardapplydetail values('5','王五','440401430122222','02');
select * from g_cardapplydetail;

1)查询身份证号为440401430103082的申请日期。
bigint转date，可以使用from_unixtime函数。

1
select a.g_applydate from g_cardapply a join g_cardapplydetail b on a.g_applyno = b.g_applyno where b.g_idcard = '440401430103082'

2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。

1
select count(g_idcard),g_idcard from g_cardapplydetail group by g_idcard having count(g_idcard) >= 2

3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。

1
2
3
4
5
6
7
8
9
10
UPDATE 
	g_cardapply
JOIN 
	g_cardapplydetail 
ON 
	g_cardapply.g_applyno = g_cardapplydetail.g_applyno 
AND
	g_cardapplydetail.g_idcard = '440401430103082'
SET g_cardapply.g_state = '07',
g_cardapplydetail.g_state = '07'

4）删除g_cardapplydetail表中所有姓李的记录。

要注意a表符合姓李的相关日期记录也需要删除

1
delete t1,t2 from g_cardapply t1 join g_cardapplydetail t2 on t1.g_applyno=t2.g_applyno where t2.g_name like '李%';

第三题

面试题3.jpg
面试题5.jpg

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop table if exists stuscore;
create table stuscore(
	name varchar(255),
	subject varchar(255),
	score int,
	stuid int
);
insert into stuscore values('张三','数学',89,1);
insert into stuscore values('张三','语文',80,1);
insert into stuscore values('张三','英语',70,1);
insert into stuscore values('李四','数学',90,2);
insert into stuscore values('李四','语文',70,2);
insert into stuscore values('李四','英语',80,2);
select * from stuscore;

表名：stuscore
1)统计如下：课程不及格[059]的多少个，良[6080]多少个，优[81-100]多少个。
多种方式：可以采用union实现，也可以采用case when then when then else and实现
case把符合的都转换成了字符串，后续可以再用group by进行分组
then是加上条件，as是起名字，count来统计条数，然后再分组

1
2
3
4
5
6
select case 
when score>=0 and score<=59 then '0-59'
when score>=60 and score<= 80 then '60-80'
when score>=81 and score<=100 then '81-100'
else '其他' end as score_range, count(*)
from stuscore group by score_range;

2)计算科科及格的人的平均成绩。

1
2
3
4
5
6

select name,avg(score) as avgscore
from stuscore 
where
 name not in(select name from stuscore where score<60)
GROUP BY name;

第四题

QQ图片20151126234632.jpg

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
drop table if exists WCMEmploy;

create table WCMEmploy(
	no int,
	name varchar(255),
	dname varchar(255),
	job varchar(255),
	sal double(10,2)
);
insert into WCMEmploy values(1, '张三', 'A', '钳工', 1500);
insert into WCMEmploy values(2, '李四', 'A', '钳工', 2800);
insert into WCMEmploy values(3, '王五', 'A', '油漆工', 3000);
insert into WCMEmploy values(4, '赵六', 'A', '水电工', 4500);
insert into WCMEmploy values(5, '钱七', 'B', '钳工', 1800);
insert into WCMEmploy values(6, '小毛', 'B', '钳工', 2600);
insert into WCMEmploy values(7, '小明', 'B', '油漆工', 2800);
insert into WCMEmploy values(8, '小刚', 'B', '水电工', 5000);
insert into WCMEmploy values(9, '孙悟空', 'C', '油漆工', 6000);
insert into WCMEmploy values(10, '猪八戒', 'C', '钳工', 2000);
insert into WCMEmploy values(11, '沙和尚', 'C', '水电工', 5000);
insert into WCMEmploy values(12, '武松', 'C', '钳工', 2000);
insert into WCMEmploy values(13, '阮小七', 'D', '水电工', 5000);
insert into WCMEmploy values(14, '哪吒', 'D', '油漆工', 2500);
insert into WCMEmploy values(15, '三太子', 'D', '钳工', 3000);
insert into WCMEmploy values(16, '龙王', 'D', '钳工', 4000);
insert into WCMEmploy values(17, '露西', 'D', '钳工', 3300);
select * from WCMEmploy;

1)请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资。

先筛选钳工，再分组，分组可以用dname和job的联合分组

1
select dname,job,avg(sal) from WCMEmploy where job='钳工' group by dname,job;

2)请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资高于2000的部门。

使用having可以对分组结果再过滤

1
select dname,job,avg(sal) from WCMEmploy where job='钳工' group by dname,job having avg(sal)>2000;

第五题

image.png
Employee是雇员信息表：
雇员姓名（主键）：person-name
街道：street
城市：city
Company是公司信息表：
公司名称（主键）：company-name
城市：city
Works是雇员工作信息表：
雇员姓名（主键）：person-name
公司名称：company-name
年薪：salary
Manages是雇员工作关系表：
雇员姓名（主键）：person-name
经理姓名：manager-name
模拟数据：
员工表：employee
image.png
公司表：company
image.png
雇员工作信息表：Works
image.png
雇员工作关系表：Manages
image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
drop table if exists Employee;
create table Employee(
	`person-name` varchar(255) primary key,
	street varchar(255),
	city varchar(255)
);
insert into Employee values('bob','街道1','天津');
insert into Employee values('frank','街道2','天津');
insert into Employee values('jack','街道3','天津');
insert into Employee values('lucy','街道4','天津');
insert into Employee values('周二','街道5','石家庄');
insert into Employee values('张三','街道6','北京');
insert into Employee values('李四','街道7','北京');
insert into Employee values('王五','街道8','北京');
insert into Employee values('赵六','街道9','石家庄');
insert into Employee values('钱七','街道10','石家庄');
select * from Employee;

drop table if exists Company;
create table Company(
	`company-name` varchar(255) primary key,
	city varchar(255)
);
insert into Company values('Small Bank Corporation', '北京');
insert into Company values('公司B', '石家庄');
insert into Company values('公司C', '天津');
select * from Company;

drop table if exists Works;
create table Works(
	`person-name` varchar(255) primary key,
	`company-name` varchar(255),
	salary double(10,2)
);
insert into Works values('bob','公司C', 22000);
insert into Works values('frank','公司C', 99999);
insert into Works values('jack','公司C', 6000);
insert into Works values('lucy','公司C', 11000);
insert into Works values('周二','公司B', 31000);
insert into Works values('张三','Small Bank Corporation', 11000);
insert into Works values('李四','Small Bank Corporation', 5000);
insert into Works values('王五','Small Bank Corporation', 8000);
insert into Works values('赵六','公司B', 12000);
insert into Works values('钱七','公司B', 21000);
select * from Works;

drop table if exists Manages;
create table Manages(
	`person-name` varchar(255) primary key,
	`manager-name` varchar(255)
);
insert into Manages values('bob','frank');
insert into Manages values('frank',NULL);
insert into Manages values('jack','lucy');
insert into Manages values('lucy','bob');
insert into Manages values('周二','jack');
insert into Manages values('张三','李四');
insert into Manages values('李四','王五');
insert into Manages values('王五','赵六');
insert into Manages values('赵六','钱七');
insert into Manages values('钱七','周二');
select * from Manages;

请给出下面每一个查询的SQL语句：

  1. 找出所有居住地与工作的公司在同一城市的员工的姓名。

    employee e
    company c
    works w
    将以上三张表进行连接，连接条件：
    e join w on e.person-name=w.person-name员工表和工作表连接
    join c on w.company-name=c.company-name
    select可以先不写 ，用where进行过滤

    1
    2
    3
    4
    5
    select e.`person-name`
    from employee e 
    join works w on e.`person-name`=w.`person-name` 
    join company c on w.`company-name`=c.`company-name`
    where e.city=c.city;

  2. 找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名。

    1
    select `person-name` from works where salary>(select max(salary) as maxsal from works where `company-name`='Small Bank Corporation');

  3. 找出平均年薪在10000美元以上的公司及其平均年薪。

    思路：按照公司进行分组，对年薪求平均值

    1
    select avg(salary) avgsal,`company-name` from works group by `company-name` having avgsal>10000;

第六题

IMG_1621.JPGIMG_1616.JPG
客户表Client
image.png
订单表Order
image.png
客户订单表ClientOrder
image.png
图书表Book
image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
drop table if exists Client;
create table Client(
	client_id int,
	client_name varchar(255),
	phone varchar(255),
	address varchar(255)
);
insert into Client values(1,'Zhao', 12522542470, '海淀区');
insert into Client values(2,'Wang', 12522542471, '朝阳区');
insert into Client values(3,'Sun', 12522542472, '大兴区');
insert into Client values(4,'Li', 12522542473, '东城区');
select * from Client;

drop table if exists `Order`;
create table `Order`(
	order_id int,
	book_id int
);
insert into `Order` values(11,21);
insert into `Order` values(12,22);
insert into `Order` values(13,23);
insert into `Order` values(14,24);
insert into `Order` values(15,21);
insert into `Order` values(16,22);
insert into `Order` values(17,23);
insert into `Order` values(18,24);
select * from `Order`;

drop table if exists ClientOrder;
create table ClientOrder(
	client_id int,
	order_id int
);
insert into ClientOrder values(1,11);
insert into ClientOrder values(1,12);
insert into ClientOrder values(2,13);
insert into ClientOrder values(2,14);
insert into ClientOrder values(3,15);
insert into ClientOrder values(3,16);
insert into ClientOrder values(4,17);
insert into ClientOrder values(4,18);
select * from ClientOrder;

drop table if exists Book;
create table Book(
	book_id int,
	book_name varchar(255),
	price double(10,2)
);
insert into Book values(21, '管理学', 30);
insert into Book values(22, '计算机网络', 50);
insert into Book values(23, '国家地理杂志', 90);
insert into Book values(24, '西游记', 20);
select * from Book;

  1. 请写出一条SQL语句，查询出每个客户的所有订单并按照地址排序，要求输出格式为：address client_name phone order_id

    分成client c
    clientorder co
    c.client_id=co.client_id 

    1
    select c.address,c.client_name,c.phone,co.order_id from client c join clientorder co on c.client_id=co.client_id order by c.address;

  2. 请写出一条SQL语句，查询出每个客户订购的图书总价。要求输出格式为：client_name total_price

    1
    2
    3
    4
    5
    select c.client_name,sum(b.price) as total_price 
    from client c join clientorder co on c.client_id=co.client_id 
    join `order` o on co.order_id=o.order_id 
    join book b on o.book_id=b.book_id 
    group by c.client_name;

  3. 如果要求每个订单可以包含多种图书，应该如何修改Order表的主键？为了保证每个订单只被一个客户拥有，应该在ClientOrder表上增加怎样的约束？

    1
    2
    #把order表的order_id和book_id做复合主键
    #加上唯一性约束：unique

第七题

image.png
image.png
模拟数据：
学生表：student
image.png
课程表：course
image.png
成绩表：sc
image.png
教师表：teacher
image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
drop table if exists student;
create table student(
	`s#` int,
	sname varchar(255),
	sage int,
	ssex char(1)
);
insert into student values(1,'学生1', 20, '男');
insert into student values(2,'学生2', 20, '男');
insert into student values(3,'学生3', 20, '男');
insert into student values(4,'学生4', 20, '男');
select * from student;

drop table if exists course;
create table course(
	`c#` int,
	cname varchar(255),
	`t#` int
);
insert into course values(1,'数学',1);
insert into course values(2,'语文',1);
insert into course values(3,'英语',2);
insert into course values(4,'政治',2);
select * from course;

drop table if exists sc;
create table sc(
	`s#` int,
	`c#` int,
	score int
);
insert into sc values(1,1,65);
insert into sc values(1,2,66);
insert into sc values(1,3,66);
insert into sc values(1,4,69);
insert into sc values(2,1,55);
insert into sc values(2,2,66);
insert into sc values(2,3,75);
insert into sc values(2,4,86);
insert into sc values(3,1,96);
insert into sc values(3,2,99);
insert into sc values(3,3,70);
insert into sc values(3,4,60);
insert into sc values(4,3,65);
insert into sc values(4,4,99);
select * from sc;

drop table if exists teacher;
create table teacher(
	`t#` int,
	tname varchar(255)
);
insert into teacher values(1,'叶平');
insert into teacher values(2,'李白');
select * from teacher;

  1. 查询1号课比2号课成绩高的所有学生学号。

    主要是c#，查询出1号和2号课的学生后，要确定两表的s#是一样的，才是同一个人，然后去对比
    select * from sc where c#=1;
    select * from sc where c#=2;

    1
    2
    3
    4
    select a.`s#` 
    from (select * from sc where `c#`=1) a 
    join (select * from sc where `c#`=2) b 
    on a.`s#`=b.`s#` where a.score>b.score;

  2. 查询平均成绩大于60分的学号和平均成绩。

    1
    select `s#`,avg(score) from sc group by `s#` having avg(score) > 60;

  3. 查询所有学生学号、姓名、选课数、总成绩。

    求选课数就是count，总成绩sum，联合分组后才可以一起显示学号姓名

    1
    2
    select  s.`s#`,s.sname,count(sc.`c#`),sum(sc.score)
    from student s join sc on s.`s#`=sc.`s#` group by s.`s#`,s.sname;

  4. 查询姓“李”的老师的个数。

    count(tname)记录不为null的个数

    1
    select count(tname) from teacher where tname like '李%';

  5. 查询没学过“叶平”老师课的学号、姓名。

    select t# from teacher where tname=’叶平’这是叶平老师的编号
    select c# from course where t#=(上面)这是查叶平老师的课的编号
    select distinct s# from sc where c# in(上面)，c#是查询的字段，in就是要查询的内容

    1
    select * from student where `s#` not in(select distinct `s#` from sc where `c#` in(select `c#` from course where `t#`=(select `t#` from teacher where tname='叶平')));

第八题

image.png
学生表：student
image.png
课程表：class
image.png
选课表：chosen_class
image.png

1

  1. 没有选修课程编号为C1的学生姓名

    select s_id from chosen_class where c_id=’c1’;选择了的
    select s_id from chosen_class where s_id not in(没选择的

1
2
select sname from student where s_id in(select s_id from chosen_class where s_id not in(select s_id from chosen_class where c_id='c1'));

  1. 列出每门课程名称和平均成绩，并按照成绩排序

    class c
    chosen_class cc
    c.c_id=cc.c_id 

    1
    select c.c_name,avg(cc.grade) as avgscore  from `class` c join chosen_class cc on c.c_id=cc.c_id group by c.c_name order by avgscore;

  2. 选了2门课以上的学生姓名。

    思路：按照学生姓名分组，计数
    student s
    chosen_class cc
    条件：s.s_id=cc.s_id

    1
    select s.sname,count(c_id) from student s join chosen_class cc on s.s_id=cc.s_id group by s.sname having count(c_id) > 2;

第九题

image.png
image.png
要转换成：
image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/*
第九题
*/
drop table if exists t_temp;
create table t_temp(
	year int,
	season varchar(255),
	count int
);
insert into t_temp values(2010,'一季度',100);
insert into t_temp values(2010,'二季度',200);
insert into t_temp values(2010,'三季度',300);
insert into t_temp values(2010,'四季度',400);
insert into t_temp values(2011,'一季度',150);
insert into t_temp values(2011,'二季度',250);
insert into t_temp values(2011,'三季度',350);
insert into t_temp values(2011,'四季度',450);
select * from t_temp;

MySQL行转列

MySQL行转列又叫做数据透视。什么叫做行转列？将原本横向排列的数据透视成纵向排列的数据，进而进行计算、分析、展示等操作。

假设有一个学生选课成绩表，包含学生姓名（stu_name）、课程名称（course_name）和分数（score）三个字段。在原始数据中，每个学生在不同的课程中都有自己的得分情况，数据样例如下：

stu_name course_name score
张三 数学 80
张三 英语 85
张三 历史 90
李四 数学 75
李四 英语 92
李四 历史 85
王五 数学 88
王五 英语 90
王五 历史 95

可以使用行转列操作，将每个学生在不同课程中的分数拆分成多条记录，每条记录包含一个课程以及对应的分数。转换后的数据样例如下：

stu_name 数学 英语 历史
张三 80 85 90
李四 75 92 85
王五 88 90 95

从上表中可以看出，在行转列之后，每一行记录都表示了一个学生在不同课程中的分数。这样更便于对不同科目的分数进行比较、计算平均值等分析操作。

使用case when+group by完成

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table if exists t_student;
create table t_student(
  stu_name varchar(10),
  course_name varchar(10),
  score int
);
insert into t_student(stu_name, course_name, score) values('张三', '数学', 80);
insert into t_student(stu_name, course_name, score) values('张三', '英语', 85);
insert into t_student(stu_name, course_name, score) values('张三', '历史', 90);
insert into t_student(stu_name, course_name, score) values('李四', '数学', 75);
insert into t_student(stu_name, course_name, score) values('李四', '英语', 92);
insert into t_student(stu_name, course_name, score) values('李四', '历史', 85);
insert into t_student(stu_name, course_name, score) values('王五', '数学', 88);
insert into t_student(stu_name, course_name, score) values('王五', '英语', 90);
insert into t_student(stu_name, course_name, score) values('王五', '历史', 95);
commit;
select * from t_student;

image.png
行转列后的效果是：
image.png
sql如下：

当course_name为数学时输出成绩，否则都输出0，然后输出最大值，就筛选出来了，列的名字都是后面起名字获得

1
2
3
4
5
6
7
8
9
select
	stu_name,
	max(case course_name when '数学' then score else 0 end) as '数学',
	max(case course_name when '英语' then score else 0 end) as '英语', 
	max(case course_name when '历史' then score else 0 end) as '历史' 
from 
	t_student
group by 
	stu_name;

通过以上内容的学习，我们这个面试题就迎刃而解了：

1
2
3
4
5
6
7
8
9
10
select
	year,
	max(case season when '一季度' then count else 0 end) as '一季度',
	max(case season when '二季度' then count else 0 end) as '二季度',
	max(case season when '三季度' then count else 0 end) as '三季度',
	max(case season when '四季度' then count else 0 end) as '四季度'
from 
	t_temp 
group by 
	year;

第十题

image.png

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table if exists t;
create table t(
	A int
);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(5);
insert into t values(6);
insert into t values(7);
insert into t values(8);
insert into t values(10);
select * from t;

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select a,(lag(a) over(order by a)) as per_a from t;
/*
将以上sql语句执行结果当做临时表m，上面的表对比a可以发现规律
筛选条件：m.a-m.per_a <> 1 or m.pre_a is null 
执行完就是左边的a表为135，右表可以先用row_number生成123
*/
select m.a,row_number() over(order by m.a)as rownum from (select a,(lag(a) over(order by a)) as per_a from t) m where m.a-m.per_a <> 1 or m.per_a is null;

select a,(lead(a) over(order by a)) as pre_a from t;
/*
将以上sql查询结果当做临时表n，这个规律可以借鉴上面的，后减前
筛选条件：n.pre_a - n.a <> 1 or n.pre_a is null
*/
select n.a,row_number() over(order by n.a) as rownum from (select a,(lead(a) over(order by a)) as pre_a from t) n where n.pre_a - n.a <> 1 or n.pre_a is null;

/*
将以上两个最终的查询结果看做两张表：x和y 
连接条件：x.rownum=y.rownum 
*/
select x.a as 开始数字,y.a as 结束数字
from (select m.a,row_number() over(order by m.a)as rownum from (select a,(lag(a) over(order by a)) as per_a from t) m where m.a-m.per_a <> 1 or m.per_a is null) x 
join (select n.a,row_number() over(order by n.a) as rownum from (select a,(lead(a) over(order by a)) as pre_a from t) n where n.pre_a - n.a <> 1 or n.pre_a is null) y 
on x.rownum=y.rownum;

解答上面这个题目需要具备以下知识点：

  • lag函数
  • lead函数
  • row_number函数

lag函数：获取当前行的上一行数据

1
select empno,ename,sal,(lag(sal) over(order by sal asc)) as pre_sal from emp;

image.png
注意：over函数用来指定“在…..范围内”，通常和lag函数联用。

lead函数：获取当前行的下一行数据

1
select empno,ename,sal,(lead(sal) over(order by sal asc)) as next_sal from emp;

image.png
注意：over函数用来指定“在…..范围内”，通常和lead函数联用。

row_number函数：可以为查询结果集生成行号：

1
select empno,ename,sal,row_number() over(order by sal) as rownum from emp;

image.png

利用row_number函数，将两个不相关的列拼接在一起显示：
image.png
image.png

1
2
3
4
5
6
7
8
select 
	x.a, y.b 
from 
	(select a,row_number() over(order by a) as rownum from t1) x 
join 
	(select b,row_number() over(order by b) as rownum from t2) y 
on 
	x.rownum = y.rownum;

image.png

CTE语法（公用表表达式）：Common Table Expression。创建临时表的一种语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查询每个部门平均工资的工资等级
-- 第一种写法
select 
	t.deptno,t.avgsal,s.grade 
from 
	(select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
	salgrade s 
on 
	t.avgsal between s.losal and s.hisal;

-- 第二种写法：使用CTE语法
with cte_exp as(select deptno,avg(sal) as avgsal from emp group by deptno)
select 
	cte_exp.deptno,cte_exp.avgsal,s.grade
from
	cte_exp
join
	salgrade s
on
	cte_exp.avgsal between s.losal and s.hisal;

partition by：将数据分区，和group by区别是：group by是分组，然后和分组函数一起用。partition by分区不需要和分组函数一起使用

1
select deptno, empno,ename,sal,(lag(sal) over(partition by deptno order by sal asc)) as pre_sal from emp;

image.png

MySQL 8.0及以上版本中支持如下常用的窗口函数：

  1. ROW_NUMBER()：排名函数，返回当前结果集中每个行的行号；
  2. RANK()：排名函数，计算分组结果中的排名，相同的行排名相同且没有空缺，下一个行排名跳过空缺；
  3. DENSE_RANK()：排名函数，计算分组结果中的排名，相同的行排名相同，排名连续，没有空缺；
  4. NTILE()：将分组结果等分为指定的组数，计算每组的大小；
  5. LAG()：返回分组内前一行的值；
  6. LEAD()：返回分组内后一行的值；
  7. FIRST_VALUE()：返回分组内第一个值；
  8. LAST_VALUE()：返回分组内最后一个值；
  9. AVG()、SUM()、COUNT()、MIN()、MAX()：聚合函数，可以配合OVER()进行窗口操作。

需要注意的是，MySQL的窗口函数和其他DBMS中的窗口函数相比较，可能略有不同，需要根据MySQL的文档进行使用。