上面一張表，表名是heyf_t10
建表SQL和資料分別在下面：
建表
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');
我的問題是查詢sql怎么寫？
要求：1。按部門組（欄位是deptid），以部門分組按組內薪資（欄位salary）的和降序排列，然后組內再以薪資降序排列，請問我這個sql怎么寫？
uj5u.com熱心網友回復：
SELECT B.* FROM(SELECT deptid,(SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) r ) AS OrderbySequence
FROM(SELECT deptid,SUM(salary) AS Sumsalary
FROM heyf_t10
GROUP BY deptid
ORDER BY SUM(salary) DESC) AS Temp1
ORDER BY Temp1.Sumsalary DESC) AS A
JOIN heyf_t10 B
ON A.deptid=B.deptid
ORDER BY A.OrderbySequence,B.salary DESC;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/30046.html
標籤：MySQL