[大数据]随笔记录力扣上的数据库题

?SQL题 中等题型 工具PL/SQL?

176.第二高的薪水一个薪水表（id,salary）SQL查询返回第二高的薪水，如果不存在第二高的薪水，查询返回null

用nvl做一个空值判断 我们加上select * from dual 语句 2.Oracle 我们从row_number() dense_rank()函数中选一个，这里我们选用dense_rank() 3.考虑到出现重复的salary 我们要在salary 前加一个聚合函数max。

select nvl(
        (select 
            max(salary)
        from 
            (select salary,dense_rank() over(order by salary desc) n from employee)
        where n=2)
       ,null) as secondhighestsalary
from dual;

177.第N高的薪水薪水表（id,salary）获取表中第n高的薪水

?写一个功能实现 create function begin end?

create function getnthhighestsalary(
n in number) 
return number is result number;
begin
    select 
        max(nvl(salary,null)) into result 
    from
        (select salary,dense_rank() over(order by salary desc) p from employee)
    where
        p = N;
    return result;
end;

178.分数排名分数相同则排名相同名次之间不应该有间隔dense_rank()?

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

180.连续出现的数字一个表logs(id,num),编写一个SQL查询，查找所有至少连续出现三次的数字

?官方答案

select 
    distinct a.num consecutivenums 
from
    logs a,logs b,logs c
where
    a.id = b.id+1 and b.id = c.id+1 
and 
    a.num=b.num and b.num=c.num

?使用开窗函数

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

184.部门工资最高的员工一个员工信息表Employee(id,name,salary,dqpartmentid),一个部门信息表Department(id,name)

编写一个 SQL 查询(有Department,Employee,salary三个字段)，找出每个部门工资最高的员工

力扣官方给出双字段子查询的方法

select 
    b.name as department
    ,a.name as employee
    ,a.salary as salary 
from 
    employee a left join department b on a.departmentid = b.id
where
    (a.departmentid,a.salary) 
in 
    (select departmentid,max(salary) from employee group by departmentid)

626.换座位一张seat座位表(id,student)，改变相邻俩学生的座位，如果学生人数是奇数，则不需要改变最后一个同学的座位

select id
    ,decode(
            mod(id,2),
            1,
            lead(student,1,student) over(order by id),
            lag(student,1) over(order by id)
            ) as student 
from seat