MySQL使用变量进行分组排序

一、MySQL使用变量进行分组排序

1.新建员工表并插入测试数据

create table employee(
id int(11) not null auto_increment primary key,
name varchar(32) default null comment '员工姓名',
sex varchar(8) default null comment '员工性别',
age int(11) default null comment '员工年龄',
department varchar(64) default null comment '员工部门'
) comment = '员工表';

insert into employee(name, sex, age, department)
values
('张一', '男', 25, '一号部门'),
('李一', '男', 35, '二号部门'),
('王一', '男', 28, '三号部门'),
('张二', '男', 18, '一号部门'),
('张三', '女', 36, '一号部门'),
('张七', '女', 31, '一号部门'),
('李二', '男', 21, '二号部门'),
('王二', '男', 27, '三号部门'),
('李三', '女', 25, '二号部门'),
('李五', '女', 29, '二号部门'),
('王三', '女', 25, '三号部门'),
('王五', '女', 32, '三号部门'),
('王四', '女', 26, '三号部门');

2.创建后的表结构如下图

3.目标：找出每个部门年龄最大的男性员工和女性员工的信息
实现SQL如下：

select a.id, a.name, a.sex, a.age, a.department, a.rank
from(
	select id, name, sex, age, department,
   	if(@department=a.department and @sex=a.sex, @rank:=@rank+1, @rank:=1) as rank,@department:=a.department, @sex:=a.sex
    from(
		select id, name, sex, age, department
		from employee
		order by department, sex, age desc
		)a,
	    (
        select @rank:=0, @department:=null, @sex:=null
		)b
	) a
where a.rank = 1
order by a.id

4.SQL解析
4.1 分组排序顾名思义就是先分组后排序，只不过是分组不再使用 group by 了；
4.2 SQL 解析

select id, name, sex, age, department
from employee
order by department, sex, age desc

首先将员工信息全部查询出来，然后根据部门，性别，年龄进行排序，这样不仅实现了单一的排序，而且对数据进行了分组，将部门相同，性别相同的信息排到了一起，并且是按照年龄进行降序排列的，这时查询出的数据如图：

这样，我们只需要将每个部门年龄最大的男性和女性员工信息提取出来就可以了。

(
   select @rank:=0, @department:=null, @sex:=null
)b

该行 SQL 只是用来简单的定义三个变量，@rank 用来记录序号， @department 用来记录下一个员工的部门， @sex 用来记录下一个员工的性别，如果你的需求需要更多的变量，继续添加即可，另如果不理解变量的定义，请查询 MySQL 变量。
使用变量分组排序的关键点是如下 SQL：

if(@department=a.department and @sex=a.sex, @rank:=@rank+1, @rank:=1) as rank,@department:=a.department, @sex:=a.sex

当第一条数据进来时，由于 @department=null，@sex=null，所以条件不成立，那么 @rank=1，
然后将该条数据的部门，性别赋给 @department， @sex，这样第二条数据进来时如果部门和性别与上一条数据的部门和性别相等，那么 @rank=2，当第三条数据进来时，部门和性别与第二条的不同，说明一个新的部门和性别出现了，那么 @rank=1，就这样执行下去，每一个部门里的男性员工和女性员工不仅区分开了，而且也各自排好顺序了，加上最后的条件

where a.rank = 1

就将每个部门年龄最大的男性员工和女性员工取出来了，因为年龄我们是按照降序排列的，所以最终结果的第一条就是我们需要的数据，同理，我们也可以出去前两条，前十条等，最终的执行结果为：

至此，使用变量进行分组排序结束。