Mysql——分组排序加序列号

来源：互联网发布：知乎便宜的女生手链编辑：程序博客网时间：2024/05/20 15:40

CREATE TABLE IF NOT EXISTS `employee` (  `empid` int(11) DEFAULT NULL,  `deptid` int(11) DEFAULT NULL,  `salary` decimal(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `employee` (`empid`, `deptid`, `salary`) VALUES(1, 10, 5500.00),(2, 10, 4500.00),(3, 20, 1900.00),(4, 20, 4800.00),(5, 40, 6500.00),(6, 40, 14500.00),(7, 40, 44500.00),(8, 50, 6500.00),(9, 50, 7500.00);

<span style="font-family: Monaco, Consolas, Courier, 'Lucida Console', monospace; font-size: 14px; line-height: 21px; white-space: pre; widows: auto;">是这样的我想实现新加一列代表每个部门的工资等级，比如</span><pre name="code" class="sql">+-------+--------+----------+| empid | deptid | salary   |+-------+--------+----------+|     1 |     10 |  5500.00 ||     2 |     10 |  4500.00 ||     3 |     20 |  1900.00 ||     4 |     20 |  4800.00 ||     5 |     40 |  6500.00 ||     6 |     40 | 14500.00 ||     7 |     40 | 44500.00 ||     8 |     50 |  6500.00 ||     9 |     50 |  7500.00 |+-------+--------+----------+//实现下面结果empid       deptid      salary         rank+-------+--------+----------+------+--------1           10          5500.00        12           10          4500.00        24           20          4800.00        13           20          1900.00        27           40          44500.00       16           40          14500.00       25           40          6500.00        39           50          7500.00        18           50          6500.00        2

SELECT empid,deptid,salary,rank FROM     (SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=ff.deptid,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.deptid     FROM        (SELECT empid,deptid,salary FROM employee GROUP BY deptid,salary ORDER BY deptid ASC, salary DESC) ff,(SELECT @rank:=0,@rownum:=0,@pa=NULL) tt) result

0 0