sql练习题

牛客练习题

题目一：获取所有非manager的员工的emp_no

法一

select a.emp_no 
from employees a
left join dept_manager b
on a.emp_no = b.emp_no
where b.emp_no is  null;

Leetcode练习题

180. 连续出现的数字

法一:自关联

SELECT distinct a.Num
from Logs a
join Logs b 
on a.Id = b.Id -1
and a.Num = b.Num
join Logs c 
on a.Id = c.Id -2 
and a.Num = c.Num;

【本题考点】

• 本题考察的是连续出现，会有同学忽略“连续”二字
• 考察对自关联的灵活应用
• 从题目连续3次成绩相等，判断出“成绩相等”和“学号连续”这2个条件。考察构建“连续学号成绩相等”的思维构建能力

链接：https://leetcode-cn.com/problems/consecutive-numbers/solution/tu-jie-mian-shi-ti-zhao-chu-lian-xu-chu-xian-nci-d/

【举一反三】
遇到类似“连续出N次的问题”可以回想本题的解答思路，如：查询至少连续3天没有出勤的员工。

法二:窗口函数

可以用lag/lead

向上窗口函数lead：取出字段名所在的列，向上N行的数据，作为独立的列
向下窗口函数lag：取出字段名所在的列，向下N行的数据，作为独立的列

窗口函数语法如下：

lag(字段名,N,默认值) over(partion by …order by …)

lead(字段名,N,默认值) over(partion by …order by …)

select distinct Num
from(
select Num,
lag(Num,1) over(order by Id) as Num1,
lag(Num,2) over(order by Id) as Num2
from Logs)t
where (t.Num = t.Num1 and t.Num = t.Num2);

具体做法可以看：
https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250661&idx=1&sn=b017344c701fbfa02a87a88a1a2207cd&chksm=835fd355b4285a43d6c55c593f83bbc7aea1bb370df8f52210bb3b3f7f5c5f304c272d863a04&token=546838497&lang=zh_CN#rd

法三:窗口函数

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM Logs) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

链接：https://leetcode-cn.com/problems/consecutive-numbers/solution/sql-server-jie-fa-by-neilsons/

注意这一题和连续三天出现的登录人数是一样的类型题目

select user_id,count(*) as serial_num
from
(select user_id, signin_date,row_number() over (partition by user_id order by signin_date) as rank 
from table) a
group by user_id, date_sub(signin_date, a.rank)
having count(*) >= 3
order by user_id;

需要注意的地方：这里使用date_sub，不使用date_diff，因为rank不是一个日期

601. 体育馆的人流量

也是一样的套路

法一：窗口函数解法 + with 函数

with t1 as (select *,id-row_number() over (order by id) as rk 
from stadium where people >= 100)
select id, visit_date,people
from t1
inner join 
(select rk 
from t1 
group by rk 
having count(rk) >= 3
on t1.rk = t2.rk;

法二：两次窗口函数

 select id, visit_date,people 
 from (select *,count(*) over (partition by t_rank) t2_rank
 from
 (select *, id- row_number() over (order by id) t_rank
 from stadium 
 where people >=100) t )t2 
 where t2.t2_rank >2;

184. 部门工资最高的员工

法一:窗口函数

select Department , Employee, Salary
from (
select d.name