Group mysql query summation and ordering and then within the group ordering-CodePudding

The above table, the table name is heyf_t10
Build SQL table and data respectively in the following:

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

Insert data

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');

My question is how do you write the query SQL?
Requirements: 1, according to the department group (field is deptid), grouped according to the group in department pay salary (field) and descending order, and then group within the salary in descending order, could you tell me how to write me the SQL?

CodePudding user response:

B. SELECT * FROM
(SELECT deptid, (SELECT @ rownum:=@ rownum + 1 FROM (SELECT @ rownum:=0) r) AS OrderbySequence
The FROM (SELECT deptid, SUM (salary) AS Sumsalary
The FROM heyf_t10
GROUP BY deptid
The ORDER BY the SUM (salary) DESC) AS Temp1
The ORDER BY Temp1. Sumsalary DESC) AS A
The JOIN heyf_t10 B
ON a. d. eptid=B.d eptid
The ORDER BY A.O rderbySequence, B.s alary DESC;