【力扣SQL】几个常见SQL题

【力扣SQL】184. 部门工资最高的员工
Employee：id（主键）、name、salary、departmentId（外键）
Department：id（主键）、name
出每个部门中薪资最高的员工：Department.name、Employee.name、Salary

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',    
    Salary
FROM
    Employee,
    Department
where 
    Employee.DepartmentId = Department.Id
and
    (Employee.DepartmentId , Salary) IN
    (   
        # 每个部门最高薪水：部门id，薪水数
        SELECT DepartmentId, MAX(Salary) as Salary
        FROM Employee
        GROUP BY DepartmentId
    )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

【力扣SQL】176. 第二高的薪水

Employee：id（主键）、salary
查询并返回表中第二高的薪水 。如果不存在第二高的薪水，返回 null

select
(
    select distinct Salary
    from Employee
    order by Salary desc
    limit 1 
    offset 1
) as SecondHighestSalary
1
2
3
4
5
6
7
8

【力扣SQL】178. 分数排名

Scores：id、score
查询并对分数从高到低进行排序。如果两个分数相等，那么两个分数的排名应该相同。在排名相同的分数后，排名数应该是下一个连续的整数。

select
  score,
  dense_rank() over (
    order by score desc
  ) as 'rank'
from
  Scores
1
2
3
4
5
6
7

【力扣SQL】180. 连续出现的数字

Logs：id（主键，自增）、num
找出所有至少连续出现三次的数字。返回的结果表中的数据可以按任意顺序排列。

select distinct
    l1.Num as ConsecutiveNums
from
    Logs l1,
    Logs l2,
    Logs l3
where
    l1.Id = l2.Id - 1
    and l2.Id = l3.Id - 1
    and l1.Num = l2.Num
    and l2.Num = l3.Num
1
2
3
4
5
6
7
8
9
10
11