力扣-数据库 175-180

1. 175 组合两个表

先熟悉熟悉擅长的数据库部分

本题思路为：组合两张表就为join，审题查看要求，发现如果不在b表中要返回null值，故应该使用left join

select
    a.firstName,
    a.lastName,
    b.city,
    b.state
from  Person a
left join Address b on a.personId =b.personId

2.176 第二高的薪水

本题思路为：如果表只有一条数据为null 其余情况根据工资倒序取第二条，可以使用开窗函数或者去掉一个最大值，再取最大值。

（1）去掉最大值

select max(a.SecondHighestSalary) as SecondHighestSalary
from (
    select  case when count(a.salary)>0 then a.salary
            else null end as SecondHighestSalary 
    from Employee a
    left join (select max(salary) as salary from Employee) b on a.salary=b.salary
    where b.salary is null 
    group by a.salary
) a

（2）开窗函数

select max(case when a.rn=2 then salary else null end) as SecondHighestSalary 
from (select salary,dense_rank() over(order by salary desc) as rn
from Employee) a

3.177 第N高的薪水

这题可以用上一题开窗函数的思维，限制排名作为一个变量就可以了。

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        /* Write your T-SQL query statement below. */
        select max(case when a.rn=@N then salary else null end) as SecondHighestSalary 
        from (select salary,dense_rank() over(order by salary desc) as rn
        from Employee) a
    );
END

4.178 分数排名

本题为对排序的使用，该问题为连续整数排序。

select score,dense_rank() over(order by score desc) as rank
from Scores

5.180 连续出现的数字

本题确认并非仅查询出现大于三次的数字，而是查询连续三次出现,选用窗口函数的方式拿下面的值。

select
distinct p.num as ConsecutiveNums
from(
    select
        num,
        lag(num,1)over(order by id) num1,
        lag(num,2)over(order by id) num2
    from Logs ) p
where p.num = p.num1 and p.num1  = p.num2

今日结束。