SQLWhere中的⼦查询-- 到每个部门⼯资最⾼的⼈(包括并列第⼀)
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 aexists子查询
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。