本文摘录了leetcode上免费的oracle sql的简单，中等题目题解，题目不多，是自己学习的随笔记录啦。

175.组合两张表

select 
    p.firstname, p.lastname, a.city, a.state 
from person p left outer join address a on p.personId = a.personId 

没什么好说的，挺简单的，左连接一下就okok了

176. 第二高的薪水

这题oracle没有limit会有点麻烦捏。

解一

参考解一

• 先取出降序的不重复的薪水

select distinct salary from empolyee order by salary desc

• 给一取出来的数据加上一列伪列rownum

第一步的时候是不能加rownum的，如果直接加的话，结果就是先排序，后select的结果，无法得到我们想要的排序序号

select
    rownum as rn, salary
from (
    select distinct salary from empolyee order by salary desc
)

• 然后让rownum = 2即可

上一步亦不可直接让rownum = 2,而是应当让rownum作为上一步所选择的表的固定一列，否则，rownum会不断的去除掉rownum = 2 然后新行依旧是rownum = 1

• 最后一步，要确保没值的时候要返回null，这里提供两种方法NVL(XXX, null) 和max，前者，在值xxx为空时会返回设定好的值null,后者要结合where使用

select NVL((select salary
from(select salary, rownum as rnum
from (
select distinct salary
from Employee
order by salary desc)
)
where rnum=2),null)
as "SecondHighestSalary" 
from dual

select max(salary) as SecondHighestSalary
from employee where salary = (select salary from (
    select rownum as rn, salary
    from(
        select distinct salary from Employee order by salary desc
    )
)
where rn = 2
)

解二

逆大天了， 根据解一，这个解也不是很难理解哈，但是解一比较快一......

    select nvl(max(salary),null) SecondHighestSalary
        from Employee
    where salary < (select max(distinct salary)        //可以没有distinct
                        from Employee)

177. 第N高的薪水

上一题的解一改一下不就好了？

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
    select max(salary) into result
    from employee where salary = (select salary from (
    select rownum as rn, salary
    from(
        select distinct salary from Employee order by salary desc
    )
    )
    where rn = N
    );
    RETURN result;
END;

sql语句学习过程中的一些语法格式还是挺烦人的，实在不行就在数据库图形界面写写或者ide连接数据库中写，好歹有个提示，不然错都不知道错哪，等熟悉了语法，就快啦

178. 分数排名

这题直接用开窗函数dense_rank(), 即可排序又能增加一列序号

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

180. 连续出现的数字

解一

利用行号 - 组内行号 = k的特点， 这是个什么特点呢，容我慢慢道来： 首先我们用开窗函数根据num分组，组内根据id排序一个列，这个就是组内行号取名叫rn，如果num是连续相同的，则rn会递增，而行号本身也是递增的，所以当rn递增时 行号 - rn 的值就会呈现出连续相同。总结一下，num连续相同->rn递增->行号 - rn = k

select 
    distinct Num as ConsecutiveNums
from 
    (select num, id - row_number() over(partition by num order by id) as rn from logs)
group by num, rn
having count(*) >= 3

其余解都差不多自连接的方式

解二

这个比较简单，但是比较慢捏。

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;

解三

Lag和lead:这个我不清楚捏

select distinct num as "ConsecutiveNums"
from (
    select num, lag(num) over(order by id) as lag, lead(num) over(order by id) as lead
    from logs) tmp
where num = lag and num = lead;

181. 超过经理收入的员工 - 力扣（LeetCode）

这个简单哦

select e.name as Employee
from Employee e where e.salary > (select salary from Employee where e.managerId = id);

182. 查找重复的电子邮箱 - 力扣（LeetCode）

这个用count 就行啦，记得要group by

select
    Email
from 
    Person
group by Email
having count(Email) > 1

183. 从不订购的客户 - 力扣（LeetCode）

这个也简单。

select 
    customers.Name as Customers
from
    Customers
where customers.Id not in (select CustomerId from Orders)

184. 部门工资最高的员工 - 力扣（LeetCode）

用下开窗函数rank()， 然后表连接一下就好了

select Department, Employee, Salary
from (select d.name Department, e.name Employee, e.salary Salary, rank() over (partition by e.departmentId order by e.salary Desc) as rn from Employee e join Department d on e.departmentId = d.id)
where rn = 1

搞这个写的累，没意思，改天再更