oracle - 使用聚合函数 Oracle 对记录进行分组

Question

我在 Oracle 中有一个表，如下所示

   CREATE TABLE Employees(EmpId INT, 
                          EmpName VARCHAR2(30), 
                          Designation VARCHAR2(30), 
                          Salary INT);

我在表中插入了如下行

INSERT ALL 
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(101, 'Scott', 'Clerk', 2500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(102, 'Mac', 'Manager', 5000)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(103, 'Steave', 'Clerk', 1500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(104, 'John', 'Clerk', 1500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(105, 'Jack', 'Analyst', 2500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(106, 'Paul', 'Manager', 4500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(107, 'Ryan', 'Clerk', 1250)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(108, 'Phillipe', 'Analyst', 3150)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(109, 'Clark', 'Clerk', 1200)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(110, 'Arnold', 'Clerk', 1100)
   SELECT * FROM dual;

下面是表格的简要概述

现在我想让收入最高的人按如下所示分组

EmpName   Designation  Salary
Phillipe  Analyst      3150 
Scott     Clerk        2500
Mac       Manager      5000

我想要指定的 empName、职务和最高薪水。

我尝试了下面的查询，但它带来了所有记录

SELECT EmpName, Designation, max(Salary) AS msal
  FROM Employees
 GROUP BY Designation, EmpName 
 ORDER BY Designation, msal DESC

谢谢回复

Answer 1

带有相关子查询的版本

SELECT EmpName, Designation, Salary
  FROM Employees e
 WHERE Salary = (SELECT MAX(Salary) 
                   FROM Employees 
                  WHERE Designation = e.Designation)

或使用JOIN

SELECT e.EmpName, e.Designation, e.Salary
  FROM Employees e JOIN 
(
  SELECT Designation, MAX(Salary) Salary
    FROM Employees 
   GROUP BY Designation
) q ON e.Designation = q.Designation
   AND e.Salary = q.Salary

这是 SQLFiddle 两个查询的演示