1. 力扣603：连续空余的座位
1.1 题目：
表: Cinema
+-------------+------+ | Column Name | Type | +-------------+------+ | seat_id | int | | free | bool | +-------------+------+ Seat_id 是该表的自动递增主键列。 在 PostgreSQL 中，free存储为整数。请使用::boolean将其转换为布尔格式。 该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。
查找电影院所有连续可用的座位。
返回按 seat_id 升序排序 的结果表。
测试用例的生成使得两个以上的座位连续可用。
结果表格式如下所示。
示例 1:
输入: Cinema 表: +---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | +---------+------+ 输出: +---------+ | seat_id | +---------+ | 3 | | 4 | | 5 | +---------+
1.2 思路：
想到用自连接的话就是一道简单题。
1.3 题解：
-- 子连接
-- 如果该座位id的free是1，并且它下一个座位的free也是1
-- 则将这两个座位id都记录下来。
with tep as (
select t1.seat_id id1, t2.seat_id id2
from Cinema t1
join Cinema t2
on t1.seat_id+1=t2.seat_id
and t1.free=1
and t2.free
), tep2 as (
-- 然后将相邻的座位id提取出来去重。
select id1 seat_id
from tep
union
select id2 seat_id
from tep
)
select *
from tep2
order by seat_id2. 牛客SQL大厂笔试真题：SQLW1：每个月Top3的周杰伦歌曲
2.1 题目：
描述
从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲
示例1
输入：
drop table if exists play_log;
create table `play_log` (
`fdate` date,
`user_id` int,
`song_id` int
);
insert into play_log(fdate, user_id, song_id)
values
('2022-01-08', 10000, 0),
('2022-01-16', 10000, 0),
('2022-01-20', 10000, 0),
('2022-01-25', 10000, 0),
('2022-01-02', 10000, 1),
('2022-01-12', 10000, 1),
('2022-01-13', 10000, 1),
('2022-01-14', 10000, 1),
('2022-01-10', 10000, 2),
('2022-01-11', 10000, 3),
('2022-01-16', 10000, 3),
('2022-01-11', 10000, 4),
('2022-01-27', 10000, 4),
('2022-02-05', 10000, 0),
('2022-02-19', 10000, 0),
('2022-02-07', 10000, 1),
('2022-02-27', 10000, 2),
('2022-02-25', 10000, 3),
('2022-02-03', 10000, 4),
('2022-02-16', 10000, 4);
drop table if exists song_info;
create table `song_info` (
`song_id` int,
`song_name` varchar(255),
`singer_name` varchar(255)
);
insert into song_info(song_id, song_name, singer_name)
values
(0, '明明就', '周杰伦'),
(1, '说好的幸福呢', '周杰伦'),
(2, '江南', '林俊杰'),
(3, '大笨钟', '周杰伦'),
(4, '黑键', '林俊杰');
drop table if exists user_info;
create table `user_info` (
`user_id` int,
`age` int
);
insert into user_info(user_id, age)
values
(10000, 18)
复制输出：
month|ranking|song_name|play_pv
1|1|明明就|4
1|2|说好的幸福呢|4
1|3|大笨钟|2
2|1|明明就|2
2|2|说好的幸福呢|1
2|3|大笨钟|1复制说明：
1月被18-25岁用户播放次数最高的三首歌为“明明就”、“说好的幸福呢”、“大笨钟”，“明明就”和“说好的幸福呢”播放次数相同，排名先后由两者的song_id先后顺序决定。2月同理。
备注：
MySQL中，日期转月份的函数为 month()，例：SELECT MONTH(‘2016-01-16') 返回 1。
2.2 思路：
在力扣也刷了一百多题sql了，想battle一下牛客的sql题，然后就一行以为很简单，但写着写着不对劲啊......运气好还是给我一次过了。
2.3 题解：
with tep1 as (
-- 先找到是周杰伦的歌曲
select song_id, song_name
from song_info
where singer_name = '周杰伦'
), tep2 as (
-- 然后再找到18到25岁人的群体
select user_id
from user_info
where age between 18 and 25
), tep3 as (
-- 在play_log过滤不符合条件的记录
select month(fdate) months, song_id
from play_log t1
where user_id in (select * from tep2)
and song_id in (select song_id from tep1)
and year(fdate) = 2022
), tep4 as (
-- 以月份和歌曲id分组，然后计算个数
select months, song_id, count(*) play_pv
from tep3
group by months, song_id
), tep5 as (
-- 使用窗口函数进行排名
select months, song_id, play_pv, rank() over (partition by months order by play_pv desc, song_id) ranks
from tep4
), tep6 as (
-- 过滤掉排名不是1， 2， 3的记录
select months `month`, ranks ranking, play_pv, song_id
from tep5
where ranks in (1, 2, 3)
)
-- 最后order by查询即可
select `month`, ranking, song_name, play_pv
from tep6 t1
join tep1 t2
on t1.song_id = t2.song_id
order by `month`, ranking3. 牛客SQL大厂笔试真题：SQLW4：获取指定客户每月的消费额
3.1 题目：
描述
某金融公司某项目下有如下 2 张表：
交易表 trade（t_id：交易流水号，t_time：交易时间，t_cus：交易客户，t_type：交易类型【1表示消费，0表示转账】，t_amount：交易金额）:
客户表 customer（c_id：客户号，c_name：客户名称）:
现需要查询 Tom 这个客户在 2023 年每月的消费金额（按月份正序显示），示例如下：
请编写 SQL 语句实现上述需求。
示例1
输入：
drop table if exists `trade` ;
CREATE TABLE `trade` (
`t_id` int(11) NOT NULL,
`t_time` TIMESTAMP NOT NULL,
`t_cus` int(16) NOT NULL,
`t_type` int(2) NOT NULL,
`t_amount` double NOT NULL,
PRIMARY KEY (`t_id`));
INSERT INTO trade VALUES(1,'2022-01-19 03:14:08',101,1,45);
INSERT INTO trade VALUES(2,'2023-02-15 11:22:11',101,1,23.6);
INSERT INTO trade VALUES(3,'2023-03-19 05:33:22',102,0,350);
INSERT INTO trade VALUES(4,'2023-03-21 06:44:09',103,1,16.9);
INSERT INTO trade VALUES(5,'2023-02-21 08:44:09',101,1,26.9);
INSERT INTO trade VALUES(6,'2023-07-07 07:11:45',101,1,1200);
INSERT INTO trade VALUES(7,'2023-07-19 06:04:32',102,1,132.5);
INSERT INTO trade VALUES(8,'2023-09-19 11:23:11',101,1,130.6);
INSERT INTO trade VALUES(9,'2023-10-19 04:32:30',103,1,110);
drop table if exists `customer` ;
CREATE TABLE `customer` (
`c_id` int(11) NOT NULL,
`c_name` varchar(20) NOT NULL,
PRIMARY KEY (`c_id`));
INSERT INTO customer VALUES(101,'Tom');
INSERT INTO customer VALUES(102,'Ross');
INSERT INTO customer VALUES(103,'Juile');
INSERT INTO customer VALUES(104,'Niki');
复制输出：
time|total
2023-02|50.5
2023-07|1200.0
2023-09|130.63.2 思路：
第一步：临时表过滤。第二步：把日期字段截取当做整体。
3.3 题解：
with tep as (
-- where过滤不是tom的记录and不是消费的记录
select t_time,
if(t_type=1, t_amount, 0) t_amount
from trade
where t_cus = (
select c_id
from customer
where c_name = 'Tom'
)
and year(t_time) = 2023
)
-- 然后截取日期的前七位。
select substring(t_time, 1, 7) time, round(sum(t_amount), 1) total
from tep
group by substring(t_time, 1, 7)
order by time
