创建数据表
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 ;
运行结果入下: