-- 找到每个部门工资最高的人(包括并列第一)
CREATE TABLE Salary(
EmpID VARCHAR(10),
DeptID VARCHAR(10),
Salary FLOAT
)
INSERT INTO Salary
SELECT 'A011','D001', 4300
UNION ALL SELECT 'A002','D001', 4300
UNION ALL SELECT 'A003','D002', 5200
UNION ALL SELECT 'A004','D002', 4600
UNION ALL SELECT 'A005','D003', 8700
UNION ALL SELECT 'A006','D003', 9300
UNION ALL SELECT 'A007','D003', 6500
UNION ALL SELECT 'A008','D004', 7700
UNION ALL SELECT 'A009','D004', 9800
UNION ALL SELECT 'A010','D004', 11000
-- 用表连接
SELECT a.* FROM Salary a
INNER JOIN(SELECT DeptID,MAX(Salary) AS Salary
FROM Salary
GROUP BY DeptID)T
ON a.DeptID=T.DeptID
AND a.Salary=T.Salary
-- 用NOT EXISTS不是太好理解
SELECT * FROM Salary a
WHERE NOT EXISTS(SELECT 1 FROM Salary b WHERE a.DeptID=B.DeptID and Salary>a.Salary )
-- 用Where中的子查询
SELECT Empid,DeptID,Salary FROM Salary a
WHERE Salary = (SELECT MAX(salary) FROM Salary b WHERE b.DeptID=a.DeptID)
ORDER BY Empid,DeptID
/* 子查询步骤
1. 外部查询获得一条记录并将其传入内部查询;
2. 基于传入的值进行内部查询
3. 内部查询将自己返回的结果值传给外部查询,外部查询利用这些值完成自己的处理。
*/
/*
Empid DeptID Salary
A002 D001 4300
A003 D002 5200
A006 D003 9300
A010 D004 11000
A011 D001 4300
*/
-- 找到每个部门工资最高的人(并列时只选一人)
SELECT MIN(EmpID) EmpID,a.DeptID,a.Salary FROM Salary a
INNER JOIN(SELECT DeptID,MAX(Salary) AS Salary
FROM Salary
GROUP BY DeptID)T
ON a.DeptID=T.DeptID
AND a.Salary=T.Salary
GROUP BY a.DeptID,a.Salary
-- 用Where中的子查询
SELECT Empid,DeptID,Salary FROM Salary a
WHERE Salary = (SELECT MAX(salary) FROM Salary b WHERE b.DeptID=a.DeptID)
AND Empid = (SELECT MIN(Empid) FROM Salary b WHERE b.Salary=a.Salary) -- 这里 别名都用b没有问题。
ORDER BY Empid,DeptID
-- 分组
SELECT MIN(Empid) Empid,DeptID,Salary FROM Salary a
WHERE Salary = (SELECT MAX(salary) FROM Salary b WHERE b.DeptID=a.DeptID)
GROUP BY DeptID,Salary
ORDER BY Empid,DeptID