SQL面试题 (二)
有一张工资表,包含三列:员工编号(ID),部门编号(GROUPS),工资(SALARY)
1. .找到每个部门工资最高的人(包括并列第一)
2. 找到每个部门工资最高的人(只选一个)
SQL语句如下:
DECLARE @G TABLE(ID INT,GROUPS NVARCHAR(20),SALARY MONEY)
INSERT INTO @G
SELECT 1,1,1000
UNION ALL SELECT 2,1,1000
UNION ALL SELECT 3,1,800
UNION ALL SELECT 4,2,2000
-- 1
SELECT *
FROM @G G
WHERE NOT EXISTS(SELECT 1 FROM @G WHERE GROUPS=G.GROUPS AND SALARY>G.SALARY)
SELECT G.*
FROM @G G
INNER JOIN
(
SELECT GROUPS,MAX(SALARY) AS SALARY
FROM @G
GROUP BY GROUPS
)T
ON G.GROUPS=T.GROUPS
AND G.SALARY=T.SALARY
-- 2
SELECT MIN(G.ID),G.GROUPS,G.SALARY
FROM @G G
INNER JOIN
(
SELECT GROUPS,MAX(SALARY) AS SALARY
FROM @G
GROUP BY GROUPS
)T
ON G.GROUPS=T.GROUPS
AND G.SALARY=T.SALARY
GROUP BY G.GROUPS,G.SALARY
一定有更好的方法,请不吝赐教。