【力扣 + 牛客 | SQL题 | 每日5题】牛客SQL热题216，217，223

也在牛客力扣写了一百来题了，个人感觉力扣的SQL题要比牛客的高三档的难度。（普遍来说）

1. 牛客SQL热题216：统计各个部门的工资记录数

1.1 题目：

描述

有一个部门表departments简况如下:

dept_no	dept_name
d001	Marketing
d002	Finance

有一个，部门员工关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	2001-06-22	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	9999-01-01
10003	32323	1996-08-03	9999-01-01

请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

dept_no	dept_name	sum
d001	Marketing	3
d002	Finance	1

示例1

输入：drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');
复制输出：d001|Marketing|3
d002|Finance|1

1.2 思路：

两个join连接。

1.3 题解：

select t1.dept_no, dept_name, count(*) sum
from dept_emp t1
join salaries t2
on t1.emp_no = t2.emp_no
join departments t3
on t1.dept_no = t3.dept_no
group by t1.dept_no
order by dept_no

2. 牛客SQL热题217：对所有员工的薪水按照salary降序进行1-N的排名

2.1 题目：

描述

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	72527	2001-12-01	9999-01-01

对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列：

emp_no	salary	t_rank
10001	88958	1
10002	72527	2
10004	72527	2
10003	43311	3

示例1

输入：drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');
复制输出：10001|88958|1
10002|72527|2
10004|72527|2
10003|43311|3

2.2 思路：

这不一眼窗口函数么。

2.3 题解：

-- 一眼窗口函数，太明显了。
select emp_no, salary, dense_rank() over (order by salary desc) ranks
from salaries
order by salary desc, emp_no

3. 牛客SQL热题223：使用join查询方式找出没有分类的电影id以及名称

3.1 题目：

描述

现有电影信息表film，包含以下字段：

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

有类别表category，包含以下字段：

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

电影分类表film_category，包含以下字段：

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

使用join查询方式找出没有分类的电影id以及其电影名称。

示例1

输入：drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (film_id smallint(5)  NOT NULL DEFAULT '0',title varchar(255) NOT NULL,description text,PRIMARY KEY (film_id));
CREATE TABLE category  (category_id  tinyint(3)  NOT NULL ,name  varchar(25) NOT NULL, `last_update` timestamp,PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (film_id  smallint(5)  NOT NULL,category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');
复制输出：3|ADAPTATION HOLES

3.2 思路：

左外连接即可。

3.3 题解：

-- 第二张表是蒙人的吧。。。
-- 这不就简单的左外连接吗
select t1.film_id, title
from film t1
left join film_category t2
on t1.film_id = t2.film_id
where category_id is null

4. 牛客大厂笔试真题W2：最长连续登录天数

4.1 题目：

描述

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”

示例1

输入：drop table if exists tb_dau;
create table `tb_dau` (`fdate` date,`user_id` int
);
insert into tb_dau(fdate, user_id)
values 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-04', 10000);
复制输出：user_id|max_consec_days
10000|2
复制说明：id为10000的用户在1月1日及1月2日连续登录2日，1月4日登录1日，故最长连续登录天数为2日备注：示例：如用户在1月3日-1月10日登录，且在1月20日-1月22日登录，则最长连续登录天数为8MySQL中日期加减的函数日期增加 DATE_ADD，例：date_add('2023-01-01', interval 1 day) 输出 '2023-01-02'
日期减少 DATE_SUB，例：date_add('2023-01-01', interval 1 day) 输出 '2022-12-31'日期差 DATEDIFF，例：datediff('2023-02-01', '2023-01-01') 输出31

4.2 思路：

一句话：两个记录的日期的差值如果等于两记录的排名差值，说明中间一直是连续的。

4.3 题解：

-- 先给每条记录以排名
with tep1 as (select fdate, user_id, rank() over (partition by user_id order by fdate) ranksfrom tb_dau
), tep2 as (-- 整体是自连接的思想-- on限制user_id相同，且diff必须是0或正数-- 最重要的条件是：两个记录的日期的差值要等于两记录的排名差值select t1.user_id, datediff(t2.fdate, t1.fdate)+1 daysfrom tep1 t1join tep1 t2on t1.user_id = t2.user_id and t2.fdate >= t1.fdate andt2.ranks = datediff(t2.fdate, t1.fdate) + t1.ranks
)select user_id, max(days) max_consec_days
from tep2
group by user_id

5. 力扣mid题550：游戏玩法分析4

5.1 题目：

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列的组合）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

结果格式如下所示：

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

5.2 思路：

窗口函数给出排名再进行简单的计算即可。

5.3 题解：

-- 先给每个人排名
with tep1 as (select player_id , event_date , dense_rank() over (partition by player_id order by event_date) ranksfrom Activity 
)
-- 然后在tep1表中将ranks为2的记录与ranks为1的记录的个数想除
-- ranks为1的记录即第一次登录的玩家，ranks为2且与第一次登录的天数相-- 差为1的记录是首次登录第二天再次登录的玩家
select round((select count(*) from tep1 t1 where ranks = 2 and datediff(event_date , (select event_date from tep1 t2 where ranks = 1 and t1.player_id = t2.player_id)) = 1)
/ 
(select count(*) from tep1 where ranks = 1), 2) fraction
from dual