数据库刷题2

1、至少连续出现三次的数字

SELECT DISTINCT(L1.num) AS ConsecutiveNums
FROM Logs L1, Logs L2, Logs L3
WHERE L1.Id + 1= L2.Id
AND L2.Id + 1= L3.Id
AND L1.num = L2.num
AND L2.num = L3.num

2、查找重复的电子邮箱

SELECT DISTINCT(P1.Email)
FROM Person P1, Person P2
WHERE P1.EmaiL = P2.Email
AND P1.Id != P2.Id

3、部门工资最高的员工

SELECT d.Name AS Department, e1.Name AS Employer, e1.Salary AS Salary
FROM Employee e1 JOIN Department d
ON e1.DepartmentId = d.Id
WHERE e1.Salary >= ALL(SELECT Salary
                       FROM Employee e2
                       WHERE e1.DepartmentId = e2.DepartmentId)

 

SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary
FROM Employee e JOIN Department d
ON e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary) IN
(SELECT Departmentid, MAX(Salary)
FROM Employee
GROUP BY DepartmentId)

4、部门工资前三高的员工

编写一个SQL查询，找出每个部门获得前三高工资的所有员工。
 

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary
FROM Employee e1 JOIN Department d
ON e1.DepartmentId = d.Id
WHERE 3 >= (SELECT COUNT(DISTINCT Salary)
            FROM Employee e2
            WHERE e1.DepartmentId = e2.DepartmentId
            AND e1.Salary <= e2.Salary)
ORDER BY Department, Employee, Salary DESC