主页 / 问题 / 1028493
Accepted
Roman Andreev
Asked:2020-09-27 12:37:59 +0000 UTC2020-09-27 12:37:59 +0000 UTC

如何修改sql查询？

  • 772
CREATE TABLE Emp ( 
  DEPTNO Int,
  SALARY Int,
  Name varchar(255)
);
INSERT INTO Emp values (10, 5000, 'KING');
INSERT INTO Emp values (30, 4000, 'BLAKE');
INSERT INTO Emp values (10, 2000, 'CLARK');
INSERT INTO Emp values (20, 2000, 'JONES');
INSERT INTO Emp values (20, 3000, 'SCOTT');
INSERT INTO Emp values (20, 3000, 'FORD');
INSERT INTO Emp values (20, 1000, 'SMITH');

有要求

SELECT max(salary), deptno, count(*) FROM Emp
GROUP BY Deptno

其中打印出部门编号、最高工资和部门人数。
如何修改查询，使显示的不是部门总人数，而是该部门领取最高工资的人数？

http://sqlfiddle.com/#!9/ab1910/4

mysql

1 个回答

  1. Best Answer
    SELECT MAX(Emp.salary), deptno, COUNT(*), SUM(Emp.salary = max_salary.salary)
FROM Emp
JOIN ( SELECT max(salary) salary, deptno
       FROM Emp
       GROUP BY deptno
     ) max_salary USING (deptno)
GROUP BY Deptno;
    • 1

相关问题