0%

sql实现小记

项目及开源学习中经常有些sql需要写,其中一些sql逻辑比较简单,但是另外的一个还是要思考一段时间的,本文就是把这部分需要思考一段时间的sql记录下来,可以与大家分享的同时,也可以避免长期未使用而遗忘。

单表自关联

分组排序累加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table tmp1(
os_type int comment '源系统',
hours varchar(4) comment '时间-小时',
count_num int comment '数量'
);
insert into tmp1 values(1, '01', 600);
insert into tmp1 values(1, '02', 500);
insert into tmp1 values(1, '03', 400);
insert into tmp1 values(1, '04', 300);
insert into tmp1 values(1, '05', 200);
insert into tmp1 values(1, '06', 100);
insert into tmp1 values(2, '01', 200);
insert into tmp1 values(2, '02', 100);
insert into tmp1 values(3, '01', 100);

求表各个os_type(系统)按照hours(时间)排序,并且累加count_num的值。单表通过os_type自关联,用时间进行过滤,然后再分组。

1
2
3
4
5
6
7
8
9
select t1.os_type
, t1.hours
, sum(t2.count_num)
from tmp1 t1
inner join tmp1 t2
on t1.os_type = t2.os_type
-- 通过where排除不符合条件的内容
where t1.hours >= t2.hours
group by t1.os_type, t1.hours

使用临时变量部分

很多从Oracle转来使用MySQL的同学都会抱怨,很多Oracle内置的函数Mysql都不支持,这部分主要讨论使用MySQL临时变量完成部分Oracle函数的功能。主要实现的函数有三种排序并标序号,[row_number ()partition over](#partition row_number), [rank() partition over](#partition rank), [dense_rank() partition over](#partition dense_rank)几个功能。

建表

首先对将要测试的数据进行建表,运行下面建表及初始化数据的sql脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-------------------------------------------------------
-- 创建测试表
-------------------------------------------------------
-- drop table if exists employee;
create table employee (
empid int comment '员工编号',
deptid int comment '部门编号',
salary int comment '工资'
);
insert into employee values(8,50,6500);
insert into employee values(14,50,6500);
insert into employee values(12,20,6500);
insert into employee values(1,10,5500);
insert into employee values(7,40,44500);
insert into employee values(3,20,1900);
insert into employee values(5,40,6500);
insert into employee values(4,20,4800);
insert into employee values(13,20,4500);
insert into employee values(2,10,4500);
insert into employee values(10,30,4500);
insert into employee values(11,20,4500);
insert into employee values(9,50,7500);
insert into employee values(9,50,4500);
insert into employee values(6,40,14500);

查看表employee中的数据,select * from employee;可以看到如图:
employee全部数据

三种排序标序号

这部分主要针对MySQL中没有分组(partition)的排序,即对选择的所有记录进行排序,其中分为简单的标号排序,不跳过排序的并列值,跳过排序的并列值。sql实现难度也从简单到困难,下面对这三种标号的排序进行说明。

单纯标号排序

单纯对工资排序,即使值重复序号也不一样,不会因为值重复就跳过重复的数量。

他的实现逻辑是编写一个按照要排序的字段(这里是salary字段)的升序后降序子查询t1,这样就能得到按照工资降序的临时子查询表t1。然后和临时变量@rank进行关联,每次在最外层查询select字段的时候,都将临时变量@rank的值加一。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-------------------------------------------------------
-- 只按照工资从高到低排名
-------------------------------------------------------
select t1.empid
, t1.deptid
, t1.salary
, @rank := @rank + 1 as rank
from
(
select empid
, deptid
, salary
from employee
order by salary desc
) t1
inner join
(
select @rank := 0
) t2
;

运行上面的sql会看到下面的结果:
排序

排序不跳过并列序号

对工资进行排序,工资相同排序相同,有并列排名时不会跳过。在单纯标号排序的基础上比较每两个相邻的salary的值,当连续的salary相同时排名相同。

先按照要排序的字段(salary)进行升序或降序,生成一个临时表t1,然后和临时变量@rank(记录当前排名),@pre_salary(记录上一条记录salary的值)进行关联。在最外层的查询中,表达式@pre_salary != (@pre_salary := salary)是判断当前的salary和@pre_salary是否相等,当两个值相等,是表达式为0,反之为1。所以外层查询选择的第四个字段相等于@rank = @rank + (0 or 1),这样就能实现当salary相同时排名相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-------------------------------------------------------
-- 对工资进行排序,工资相同排序相同,但不会跳过排名
-------------------------------------------------------
select t1.empid
, t1.deptid
, t1.salary
-- @rank = @rank + (0 or 1) 如果表达式@pre_salary != (@pre_salary := salary)为True就是1 否则就是0
, @rank := @rank + (@pre_salary != (@pre_salary := salary)) as rank
from
(
select empid
, deptid
, salary
from employee
order by salary desc
) t1
inner join
(
-- @rank记录当前的排名 @pre_salary记录salary的上一个值
select @rank := 0
, @pre_salary := -1
) t2
;

运行上面的sql会看到下面的结果:
排序_重复不跳过序号

排序跳过并列序号

对工资进行排序,工资相同排序相同,有并列排名时会跳过。排序跳过并列序号是排序不跳过并列序号的升级版,其难点是要比较两个连续salary是否相等的同时,记录连续salary出现的次数@skip_num。当两个连续salary不相等的时候,@rank的值除了要加一外,还要加上@skip_num的值。

先生成按照salary降序的临时表t1,临时变量@rank用于计算当前排名,@skip_num用于记录连续相同salary的次数,@pre_salary_1@pre_salary_2均用于判断当前记录salary和前一条记录的salary是否相等,前者为@rank服务,后者为@skip_num服务。

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
-------------------------------------------------------
-- 对工资进行排序,工资相同排序相同,有并列排名时会跳过
-------------------------------------------------------
select t3.empid
, t3.deptid
, t3.salary
, t3.rank
from
(
select t1.empid
, t1.deptid
, t1.salary
-- 当前值和之前的值一样,即@pre_salary_1 = (@pre_salary_1 := salary)时 取之前的排名@rank 不一样时取当前排名@rank+1+之前跳过的排名@skip_num
, if (
@pre_salary_1 = (@pre_salary_1 := salary), @rank, @rank := @rank + 1 + @skip_num
) as rank
-- @skip_num保存同一排名出现的次数 @pre_salary_2 = (@pre_salary_2 := salary)是就说明同一排名出现一次
, if (
@pre_salary_2 = (@pre_salary_2 := salary), @skip_num := @skip_num + 1, @skip_num := 0
) as _skip_num
from
(
select empid
, deptid
, salary
from employee
order by salary desc
) t1
inner join
(
-- @rank记录当前的排名 @pre_salary_1和@pre_salary_2记录salary的前一个值 @pre_salary_1用于计算排名是否增加 @pre_salary_2是中间变量确定排名跳过的数量,即@skip_num
select @rank := 0
, @pre_salary_1 := -1
, @pre_salary_2 := -1
, @skip_num := 0
) t2
) t3
;

运行上面的sql会看到下面的结果:
排序_重复跳过序号

实现oracle相关分析函数功能

这部分主要实现了Oracle相关的分析函数,包括[row_number](#partition row_number), [rank](#partition rank), [dense_rank](#partition dense_rank)三个分析函数。

partition row_number

实现Oracle的row_number() partition over方法,要达到的目的是先对源数据按其中一个字段(deptid)进行分组,再在分组中按照另一个字段(salary)升序或降序排序,并进行标号。

其实现逻辑是,生成一张按照deptid和salary排序的临时表,然后和临时变量@pre_dept@rank进行关联,其中@pre_dept表示前一条记录部门的值,@rank表示排名。主要的实现在外层查询的if (@pre_dept = (@pre_dept := deptid), @rank := @rank + 1, @rank := 1)中,如果当前deptid和前一条记录的deptid相同时,说明是在同一个deptid的分组中,排名@rank就增加1;如果当前的deptid和前一条记录的deptid不相同,说明deptid分组已经变化了,排名@rank要重新从1开始计算,所以@rank:=1

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
-------------------------------------------------------
-- Oracle row_number方法
-- 按照部门分组 每组进行排序
-- 排序逻辑 按工资高低排序 同样工资不同顺序
-------------------------------------------------------
select t1.empid
, t1.deptid
, t1.salary
-- 如果当前deptid(即@pre_dept := deptid) != 之前的deptid(即@pre_dept) 就令当前排序初始化为1(即@rank := 1);
-- 如果等于之前的deptid 就在原来的排序上加一(即@rank := @rank + 1)
, if (
@pre_dept = (@pre_dept := deptid), @rank := @rank + 1, @rank := 1
) as _row_number
from
(
-- 第一步 通过这里控制 以deptid分组 以salary降序
select empid
, deptid
, salary
from employee
order by deptid, salary desc
) t1
inner join
(
-- 此处要选一个异常值(不会在数据中出现的值) 尽量别用null 因为null有陷阱
select @pre_dept := -1
, @rank := -1
) t2
;

运行上面的sql会看到下面的结果:
row_number_partition_over

partition rank

实现Oracle的rank() partition over方法,是[row_number](#partition row_number)的升级版。要达到的目的是先对源数据按其中一个字段(deptid)进行分组,再在分组中按照另一个字段(salary)升序或降序排序,并进行排序,此时的排序允许有并列序号,但是不会跳过并列序号的次数。

最外层查询只是为了选择@pre_salary变量的值,主要逻辑集中在次外层查询。首先在employee表中按照部门和工资排序,得到子查询t1,再和@pre_dept(前一条记录的部门), @rank(排名), @pre_salary(前一条记录的工资)关联。在次外层查询中,满足当前部门(deptid)和前一条记录的部门(@pre_salary)相等,并且当前记录工资(salary)和前一条记录工资(@pre_salary)相等时,说明是同一个部门并且工资相同,所以排序(@rank)值不变;仅是部门相等但是工资不等,则说明同一部门但是工资不同,所以@rank + 1;回到sql次外层查询中最外层的if条件,当前部门和前一条记录的部门不相等时,则部门变了,排序要重新开始,所以@rank := 1。注意次外层查询中,单独@pre_salary := salary进行赋值,防止当外层if条件不满足时,在内层sql写@pre_salary = (@pre_salary := salary)不能更新@pre_salary的值。

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
-------------------------------------------------------
-- 实现Oracle的rank() partition over方法
-------------------------------------------------------
select t3.empid
, t3.deptid
, t3.salary
, t3._rank_over
from
(
select t1.empid
, t1.deptid
, t1.salary
-- 如果当前deptid(即@pre_dept := deptid) != 之前的deptid(即@pre_dept) 就令当前排序初始化为1(即@rank := 1);
-- 如果等于之前的deptid 就在原来的排序上加一(即@rank := @rank + 1)
, if (
@pre_dept = (@pre_dept := deptid)
-- 如果部门相等 比较同部门的工资是否相等
, if (
@pre_salary = salary, @rank, @rank := @rank + 1
)
, @rank := 1
) as _rank_over
-- 单独保存本次salary的值 放在@pre_dept = (@pre_dept := deptid)里面会因为条件不一样导致结果不同
, @pre_salary := salary
from
(
-- 第一步 通过这里控制 以deptid分组 以salary降序
select empid
, deptid
, salary
from employee
order by deptid, salary desc
) t1
inner join
(
-- 此处要选一个异常值(不会在数据中出现的值) 尽量别用null 因为null有陷阱
select @pre_dept := -1
, @rank := -1
, @pre_salary := -1
) t2
) t3
;

运行上面的sql会看到下面的结果:
rank_partition_oreder

partition dense_rank

实现Oracle的dense_rank() partition over方法,是[rank](#partition rank)方法的升级版。在rank方法的基础上,如果有重复的序号,要跳过重复的次数。

先按照部门和工资排序,生成子查询t1。和临时变量@pre_dept(上一条记录部门的值),@rank(当前数据排名),@pre_salary(上一条记录工资的值),@skip_num(该部门分组该排名需要跳过的值)进行关联。其中排名实现在[rank](#partition rank)方法上加上@skip_num值,所以剩下的问题是确定@skip_num的值。若当前工资的值和上一条记录中工资的值相等,@skip_num的值就加一,反之@skip_num均赋值为0

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
-------------------------------------------------------
-- oracle dense_rank() over 方法
-------------------------------------------------------
select t3.empid
, t3.deptid
, t3.salary
, t3._dense_rank_over
from
(
select t1.empid
, t1.deptid
, t1.salary
-- 如果当前deptid(即@pre_dept := deptid) != 之前的deptid(即@pre_dept) 就令当前排序初始化为1(即@rank := 1);
-- 如果等于之前的deptid 就在原来的排序上加一(即@rank := @rank + 1)
, if (
@pre_dept = (@pre_dept := deptid)
-- 如果部门相等 比较同部门的工资是否相等
, if (
@pre_salary = salary, @rank, @rank := @rank + 1 + @skip_num
)
, @rank := 1
) as _dense_rank_over
-- 单独保存本次salary的值 放在@pre_dept = (@pre_dept := deptid)里面会因为条件不一样导致结果不同
, if (
@pre_salary = salary, @skip_num := @skip_num + 1, @skip_num := 0
) as _skip_num
, @pre_salary := salary
from
(
-- 第一步 通过这里控制 以deptid分组 以salary降序
select empid
, deptid
, salary
from employee
order by deptid, salary desc
) t1
inner join
(
-- 此处要选一个异常值(不会在数据中出现的值) 尽量别用null 因为null有陷阱
select @pre_dept := -1
, @rank := -1
, @pre_salary := -1
, @skip_num := 0
) t2
) t3
;

运行上面的sql会看到下面的结果:
dense_rank_partition_order