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 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 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