mysql 组内排序的实现

这是一道笔试时候遇到的手写sql语句的题目：

要求：查出表中相同的机构、单位下，10,11月两个月工资总额排名第一第二的客户

源数据如下图所示：

建表语句：

CREATE TABLE `tabletest` (  `open_inst_id` varchar(20) default NULL,  `Merch_id` varchar(20) default NULL,  `cust_id` char(20) default NULL,  `salary` int(20) default NULL,  `datatime` varchar(20) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据：

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','001','3000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','001','3500','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','001','3500','201811');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','002','5000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','002','5000','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','002','5500','201811');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','003','4000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','003','4000','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007331','01','003','4000','201811');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','004','2000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','004','2000','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','004','2000','201811');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','005','6000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','005','6000','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','005','6000','201811');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','006','5000','201809');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','006','5500','201810');

INSERT INTO `test`.`tabletest`(`open_inst_id`,`Merch_id`,`cust_id`,`salary`,`datatime`) VALUES ( '10007000','02','006','5000','201811');

实现步骤：

/**查出表中相同的机构、单位下，10,11月两个月工资总额排名第一第二的客户*/

1.筛选10.11月份的数据

SELECT open_inst_id,Merch_id,cust_id,SUM(salary)count_salary FROM tabletest WHERE datatime IN ('201810','201811') GROUP BY cust_id ORDER BY count_salary DESC

2.按部门和总薪资进行分组

SELECT * FROM

(SELECT open_inst_id,Merch_id,cust_id,SUM(salary)count_salary

      FROM tabletest WHERE datatime IN ('201810','201811')

      GROUP BY cust_id ORDER BY count_salary DESC LIMIT 4)m1

ORDER BY m1.Merch_id,count_salary  DESC

实现结果：