【SQL】刷题整理[五]

一、力扣178. 分数排名

我们可以了解下有关排等级的几个函数，如下所示：

解决本题的代码如下：

select Score,dense_rank() over (order by Score desc) as 'Rank' 
from Scores;

二、力扣180. 连续出现的数字

代码如下：

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
;

三、力扣183. 从不订购的客户

使用左连接进行查询

代码如下：

select a.name as Customers
from Customers as a left join orders as b
on a.id = b.CustomerId
where b.id is null;

四、力扣184. 部门工资最高的员工

代码如下：

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

五、力扣197. 上升的温度

代码如下：

select b.Id 
from Weather a join Weather b 
where DATEDIFF(b.recordDate,a.recordDate)=1
and b.Temperature > a.Temperature;

🐱‍🏍🐱‍🏍🐱‍🏍

时代在进步，旺财还需努力努力再努力！