SQL实现分组排序和组内排序（相同分数并列排名）

向右看齐 2022-06-09 11:11 414阅读 0赞

创建数据表

CREATE TABLE `heyf_t10` ( `empid` int(11) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据

INSERT INTO `heyf_t10` VALUES ('1', '10', '5500.00');
    INSERT INTO `heyf_t10` VALUES ('2', '10', '4500.00');
    INSERT INTO `heyf_t10` VALUES ('3', '20', '1900.00');
    INSERT INTO `heyf_t10` VALUES ('4', '20', '4800.00');
    INSERT INTO `heyf_t10` VALUES ('5', '40', '14500.00');
    INSERT INTO `heyf_t10` VALUES ('6', '40', '14500.00');
    INSERT INTO `heyf_t10` VALUES ('7', '40', '44500.00');
    INSERT INTO `heyf_t10` VALUES ('8', '50', '6500.00');
    INSERT INTO `heyf_t10` VALUES ('9', '50', '7500.00');
    INSERT INTO `heyf_t10` VALUES ('10', '40', '14000.00');
    INSERT INTO `heyf_t10` VALUES ('11', '40', '14500.00');
    INSERT INTO `heyf_t10` VALUES ('12', '40', '14550.00');
    INSERT INTO `heyf_t10` VALUES ('13', '40', '14550.00');

实现按照部门分组，salary为排序项

select empid,deptid,salary,rank from ( 
            select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 , 
                   if(@pdept=heyf_tmp.deptid AND @salary>=heyf_tmp.salary,@rank:=@rank+1,@rank:=1) as rank1, 
                   if(@pdept=heyf_tmp.deptid AND @salary=heyf_tmp.salary,@rank:=@rank-1,@rank) as rank,
                   @pdept:=heyf_tmp.deptid,
                   @salary:=heyf_tmp.salary
             from (  
                   select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc  
             ) heyf_tmp ,
            (select @rownum :=0 , @pdept := null ,@rank:=0) a 
     ) result ;

运行结果入下：

![这里写图片描述][SouthEast]

[SouthEast]: /images/20220609/73a9fd669943499f9a860ac3d7781329.png