SQL看一万遍都没有刷一题有用

Leetcode

# Write your MySQL query statement below

178. 分数排名

select
    score
    ,dense_rank() over(order by score DESC) as `rank`
from Scores
order by score DESC

• ⚠️注意：rank 是 SQL 关键字，MySQL 需要用反引号 rank 包裹。

180. 连续出现的数字

#01连续

进阶：601，3580，3832

方法1：

select
    t.num as ConsecutiveNums
from Logs t
join Logs t2 on t.id = t2.id - 1 and t.num = t2.num
join Logs t3 on t.id = t3.id - 2 and t.num = t3.num
group by 1

• 将表自连接三次，分别表示连续的三行
• 连接条件：id连续，数字相同
• 使用group by去重（比如连续出现4次”1”会产生多条记录）

👍方法二（通用常用）：

with tmp as(
    select id, num, id - row_number() over(partition by num order by id) as grp
    from Logs
)
select
    distinct num as ConsecutiveNums
from tmp
group by num, grp
having count(*) >= 3

• ==这种求连续xx的题目，都有一个套路，就是利用row_number()，作差==
• row_number(partition by num order by id)为每个数字按id排序编号
• id - 行号 如果数字连续出现，这个差值是恒定的（形成相同的组）
• 按 num 和 grp 分组，统计每组数量 >= 3 的即可

中间过程：

• num=1 分区内 row_number：

  id=1 → rn=1 → grp=0

  id=2 → rn=2 → grp=0

  id=3 → rn=3 → grp=0

  id=5 → rn=4 → grp=1

• id1,2,3 grp 相同，代表连续；id5 属于另一组

183. 从不订购的客户

同类：607

方法1：最常用 left join + is null

select name as Customers 
from Customers t1
left join Orders t2 on t1.id=t2.customerId
where t2.customerId is null

方法2：not exists

select name as Customers 
from Customers t1
where not exists(
    select 1
    from Orders t2
    where t1.id=t2.customerId
)

185. 部门工资前三高的所有员工

#困难 可能想表达难在dense_rank()

with tmp as(
    select 
        t2.name as Department
        ,t1.name as Employee
        ,t1.salary as Salary
        ,dense_rank() over(partition by t2.name order by t1.salary desc) as dense_rnk
    from Employee t1
    left join Department t2 
    on t1.departmentID = t2.id
)
select
    Department
    ,Employee
    ,Salary
from tmp
where dense_rnk <= 3
;

196. 删除重复的电子邮箱

#简单

方法一：使用子查询 + MIN()

delete from Person
where id not in(
    select min_id from(
        select min(id) as min_id from Person group by email
    ) as t
);

• 用email分组，保留最小的id
• mysql不能同时删除+查找

方法二：使用自连接（JOIN）

# Write your MySQL query statement below
delete p1 from Person p1
inner join Person p2
on p1.email=p2.email and p1.id>p2.id;

• 以email自连接，删除id更大的

197. 上升的温度

#简单

select t1.id
from Weather t1
join Weather t2 on t1.recordDate=date_add(t2.recordDate, interval 1 day)
and t1.temperature > t2.temperature

• 自连接
• 日期关联：确保t1是t2的后一天
• 温度比较：筛选温度升高的记录

262. 行程和用户

with tmp as(
    select a.status,a.request_at
    from Trips a
    join Users b on a.client_id=b.users_id and b.banned='NO'
    join Users c on a.driver_id=c.users_id and c.banned='NO'
    where a.request_at>='2013-10-01' and a.request_at<='2013-10-03'
)
select
    request_at as Day
    ,round(
        sum(case when status in('cancelled_by_driver','cancelled_by_client') then 1 else 0 end)*1.0 / count(*) ,2
    ) as `Cancellation Rate`
from tmp
group by 1

• 乘客和司机都必须未被禁止👉内连接两次
• Cancellation Rate 反引号包裹 带空格别名

511. 游戏玩法分析 Ⅰ

#简单

select
    player_id
    ,min(event_date) as first_login
from Activity
group by 1

584. 寻找用户推荐人

#简单

select name
from Customer
where referee_id <> 2 or referee_id is null

• 方法： 使用 <> (!=) 和 IS NULL

586. 订单最多的客户

#简单

select customer_number
from Orders
group by 1
order by count(*) DESC
limit 1

• limit 1只能用在无并列的情况下

进阶：

如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

with tmp as(
	select
    	customer_number
    	,count(*) as cnt
    	,rank() over(order by count(*) DESC) as rnk
    from Orders
    group by 1
)
select customer_number
from tmp
where rnk=1

• rank()跳跃排名：1, 2, 2, 4, 5…

596. 超过 5 名学生的课

#简单

select class
from Courses
group by 1
having count(distinct student) >= 5

• 直接count class 可能会忽略重修的学生

601. 体育馆的人流量

#01连续

with qualified as (
    select *,id - row_number() over(order by id) as grp
    from (
        select * from Stadium
        where people>=100
    ) t
)
,grp_cnt as(
    select *,count(*) over(partition by grp) as cnt
    from qualified
)
select
    id,visit_date,people
from grp_cnt
where cnt>=3
order by visit_date

• 连续键 - row_number() over(order by 连续键) as grp

603. 连续空余座位

收费中

607. 销售员

#简单

👍方法1：最常用 left join + is null

select t.name
from SalesPerson t
left join (
    select distinct a.sales_id
    from Orders a
    join Company b on a.com_id=b.com_id
    where b.name='RED'
)t2 on t.sales_id=t2.sales_id
where t2.sales_id is null

• 一定要加 distinct，防止同一个销售多条 RED 订单重复匹配

方法2：not exists

select t.name
from SalesPerson t
where not exists(
    select 1
    from Orders a
    join Company b on a.com_id=b.com_id
    where b.name='RED'
    and a.sales_id=t.sales_id
)

• 先想思路，不要全部连接，运用子查询更简单

not in和not exists的语法和区别？

一、NOT IN 语法

SELECT 列名
FROM 表A
WHERE 列名 NOT IN (子查询结果集);

示例：

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT sales_id          -- 子查询必须返回单列
    FROM Orders o
    JOIN Company c ON o.com_id = c.com_id
    WHERE c.name = 'RED'
);

⚠️ 重要注意：

• 子查询返回的列中不能有 NULL，否则结果会是空集（因为与NULL比较返回UNKNOWN）
• 适用于子查询结果集较小的情况

二、NOT EXISTS 语法

SELECT 列名
FROM 表A
WHERE NOT EXISTS (子查询);

示例：

SELECT name
FROM SalesPerson s          -- 需要给主查询表起别名
WHERE NOT EXISTS (
    SELECT 1                -- SELECT什么不重要，通常写1或*
    FROM Orders o
    JOIN Company c ON o.com_id = c.com_id
    WHERE o.sales_id = s.sales_id   -- 关键：这里要关联主查询的表
      AND c.name = 'RED'
);

关键点：

• 子查询中必须有关联条件（如 o.sales_id = s.sales_id）
• 子查询返回空结果时，NOT EXISTS 为真
• 不受 NULL 影响

针对本题的NOT EXISTS 写法（推荐，更安全）

SELECT name
FROM SalesPerson s
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    JOIN Company c ON o.com_id = c.com_id
    WHERE o.sales_id = s.sales_id 
      AND c.name = 'RED'
);

执行逻辑图解

NOT IN 逻辑：
1. 先执行子查询，得到集合：{1, 4}  （向RED销售的sales_id）
2. 检查主查询：sales_id 是否在 {1, 4} 中？
3. 返回不在集合中的：2, 3, 5 → Amy, Mark, Alex

NOT EXISTS 逻辑：
1. 遍历SalesPerson每一行（如John, sales_id=1）
2. 检查是否存在：sales_id=1 且 公司=RED 的订单？
3. 存在 → 排除；不存在 → 保留
4. 最终保留：Amy, Mark, Alex

结论： 对于本题，两种写法结果相同，

620. 有趣的电影

#简单

select * from cinema
where description <> 'boring'
and id % 2 = 1
order by rating DESC

• 判断奇数的方法：取模 id % 2 = 1

627. 变更性别

#简单

方法一：case表达式

update Salary
set sex = case sex
    when 'f' then 'm'
    when 'm' then 'f'
end

• case when 中间一定要加列名

方法二：if函数

update Salary
set sex = if(sex='f', 'm', 'f')

update语句的语法？

UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2, ...
[WHERE 条件]  # 可选（省略则更新所有行）

case when语句的语法？

本题，语法也支持写成：

update Salary
set sex = case
    when sex='f' then 'm'
    when sex='m' then 'f'
end

1084. 销售分析 III

#简单

select t1.product_id, t2.product_name
from Sales t1
left join Product t2
    on t1.product_id=t2.product_id
group by 1
having min(t1.sale_date) >= '2019-01-01'
    and max(t1.sale_date) <= '2019-03-31'

• 正解：min和max

1141. 查询近30天活跃用户数

#简单

select
    activity_date as day
    ,count(distinct user_id) as active_users
from Activity
where activity_date > '2019-07-27' - interval 30 day
    and activity_date <= '2019-07-27'
group by 1

• 截至 2019-07-27（包含），近 30 天
  • activity_date > '2019-07-27' - interval 30 day
  • activity_date > date_sub('2019-07-27', interval 30 day)

1148. 文章浏览 I

#简单

select distinct author_id  as id
from Views
where author_id  = viewer_id
order by 1

• 去重可以用distinct，也可以用group by

1179. 重新格式化部门表

#简单

select id
    ,sum(case month when 'Jan' then revenue end) as Jan_Revenue
    ,sum(case month when 'Feb' then revenue end) as Feb_Revenue
    ,sum(case month when 'Mar' then revenue end) as Mar_Revenue
    ,sum(case month when 'Apr' then revenue end) as Apr_Revenue
    ,sum(case month when 'May' then revenue end) as May_Revenue
    ,sum(case month when 'Jun' then revenue end) as Jun_Revenue
    ,sum(case month when 'Jul' then revenue end) as Jul_Revenue
    ,sum(case month when 'Aug' then revenue end) as Aug_Revenue
    ,sum(case month when 'Sep' then revenue end) as Sep_Revenue
    ,sum(case month when 'Oct' then revenue end) as Oct_Revenue
    ,sum(case month when 'Nov' then revenue end) as Nov_Revenue
    ,sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by 1

• 注意：聚合函数，sum作用是为了group by，选择max,min,avg同样可以

表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+

1527. 患某种疾病的患者

#简单

select * from Patients
where conditions like 'DIAB1%' 
    or conditions like '% DIAB1%'

• 主键 = 唯一标识每一行的字段，不能重复，不能为空

1581. 进店却未进行过交易的顾客

select 
    t1.customer_id
    ,count(*) as count_no_trans
from Visits t1
left join Transactions t2 on t1.visit_id=t2.visit_id
where t2.visit_id is null
group by 1

• left join+is null

1667. 修复表中的名字

#简单

select
    user_id
    ,concat(
        upper(substring(name, 1, 1))
        ,lower(substring(name, 2))
    ) as name
from Users
order by 1

• concat字符串拼接：concat(string1, string2, ..., stringN)
• upper转大写：upper(string)
• lower转小写：lower(string)
• substring / substr 截取子串：substring(string, start, length)，起始从1开始，长度可选，默认到末尾

1795. 每个产品在不同商店的价格

#简单

select * from(
select product_id, 'store1' as store, store1 as price
from Products

union all

select product_id, 'store2' as store, store2 as price
from Products

union all

select product_id, 'store3' as store, store3 as price
from Products
) t
where price is not null

• 经典的 SQL 数据转换问题，需要将列转行（宽表转长表）
• union all不去重效率更高

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+

1873. 计算特殊奖金

#简单

select
    employee_id
    ,case when employee_id % 2 = 1 and substring(name, 1, 1) <> 'M' then salary else 0 end as bonus
from Employees
order by 1

3580. 寻找持续进步的员工

#01连续 ?好像不是同类题

方法1：

-- 最新3次评估
with cnt_3 as(
    select 
        employee_id
        ,review_date
        ,rating
        ,row_number() over(partition by employee_id order by review_date DESC) as rn
    from performance_reviews
    where employee_id in (
        select employee_id 
        from performance_reviews 
        group by employee_id
        having count(*)>=3
    )
)
,rn_3 as(
    select
        employee_id
        ,max(case when rn=3 then rating end) as r1
        ,max(case when rn=2 then rating end) as r2
        ,max(case when rn=1 then rating end) as r3
    from cnt_3
    where rn<=3
    group by 1
    having r1<r2 and r2<r3
)
select
    t1.employee_id
    ,t2.name
    ,t1.r3-t1.r1 as improvement_score 
from rn_3 t1
join employees t2 on t1.employee_id=t2.employee_id
order by improvement_score DESC,t2.name ASC

• 如果按照提示一步步反而写复杂了

👍方法2：

with ranked as (
    select
        employee_id
        ,rating as r1
        ,lead(rating,1) over(partition by employee_id order by review_date DESC) as r2
        ,lead(rating,2) over(partition by employee_id order by review_date DESC) as r3
        ,row_number() over(partition by employee_id order by review_date DESC) as rn
    from performance_reviews
)
select
    t.employee_id
    ,t2.name
    ,t.r1-t.r3 as improvement_score
from ranked t
join employees t2 on t.employee_id=t2.employee_id
where t.rn=1
    and t.r3 is not null
    and t.r1 > t.r2
    and t.r2 > t.r3
order by improvement_score DESC,t2.name ASC;

• 其实最后只要最新三次评估的记录：用row_number + lead

3611. 查找超预订员工

with week_sum as(
    select
        employee_id
        ,date_sub(meeting_date, interval weekday(meeting_date) day) as week_start
        ,sum(duration_hours) as sum_hours
    from meetings
    group by 1,2
)
select
    t.employee_id
    ,t2.employee_name
    ,t2.department
    ,sum(case when sum_hours > 20 then 1 else 0 end) as meeting_heavy_weeks
from week_sum t
join employees t2 on t.employee_id=t2.employee_id
group by 1,2,3
having meeting_heavy_weeks >= 2
order by meeting_heavy_weeks DESC,employee_name ASC

• 每周分组
• date_sub(日期列, interval weekday(日期列) day) 得到每个周一的日期，用这个日期作为分组标识

3705. 寻找黄金时段客户

with customer_metrics as(
    select
        customer_id
        ,count(order_id) as total_orders
        ,sum(
            case when (TIME(order_timestamp) between '11:00:00' and '14:00:00' 
            or TIME(order_timestamp) between '18:00:00' and '21:00:00') then 1 end
        ) as peak_cnt
        ,sum(if(order_rating is not null,1,0)) as rated_cnt
        ,sum(order_rating) as sum_rating
    from restaurant_orders
    group by 1
    having total_orders>=3
        and rated_cnt>0
        and peak_cnt*10 >= total_orders*6
        and rated_cnt*2 >= total_orders
)
select
    customer_id
    ,total_orders
    ,round(peak_cnt*100.0 / total_orders, 0) as peak_hour_percentage
    ,round(sum_rating*1.0 / rated_cnt, 2) as average_rating
from customer_metrics
where sum_rating*1.0/rated_cnt >=4.0
order by average_rating DESC,customer_id DESC;

• 思路拆解

  • CTE 分组统计每个客户指标
    • total_orders：总订单数（COUNT (order_id)）
    • peak_cnt：高峰时段订单数（小时 11-14 或 18-21）
    • rated_cnt：有评分的订单数（order_rating IS NOT NULL）
    • sum_rating：所有有效评分总和
  • 衍生计算：
    • peak_hour_percentage = 高峰订单 / 总订单 *100，保留 0 位小数
    • average_rating = 总分 / 有效评分数，保留 2 位小数

• 小时提取：TIME(order_timestamp) 直接获取时间戳的 时间部分

• 百分比计算：必须乘 1.0 / 100.0 浮点运算，避免整数除法丢失小数

• NULL 评分处理：SUM(CASE WHEN order_rating IS NOT NULL) 只统计有效评分

3716. 寻找流失风险客户

with user_latest as(
    select * from(
        select
            user_id
            ,plan_name as current_plan
            ,monthly_amount as current_monthly_amount
            ,event_type as last_event_type
            ,row_number() over(partition by user_id order by event_date DESC) as rn
        from subscription_events
    ) t
    where rn=1
)
,user_agg as(
    select
        t1.user_id
        ,t2.current_plan
        ,t2.current_monthly_amount
        ,t2.last_event_type
        ,sum(case when t1.event_type='downgrade' then 1 else 0 end) as cnt_down
        ,max(t1.monthly_amount) as max_historical_amount
        ,datediff(max(event_date),min(event_date)) as days_as_subscriber
    from subscription_events t1
    inner join user_latest t2 on t1.user_id=t2.user_id
    group by 1,2,3,4
)
select
    user_id
    ,current_plan
    ,current_monthly_amount
    ,max_historical_amount
    ,days_as_subscriber
from user_agg
where 
    last_event_type != 'cancel'
    and cnt_down >= 1
    and current_monthly_amount*2 < max_historical_amount
    and days_as_subscriber >= 60
order by days_as_subscriber DESC, user_id ASC;

• rn=1 就是该用户最新一条订阅事件
• 铁律：聚合和窗口要分开！

3832. 查找具有持续行为模式的用户

#困难 #01连续

表 activity 结构：

user_id	action	action_date
用户 ID	行为类型	行为发生日期

需求：找出每个用户每种行为连续 5 天及以上的连续记录，每个用户只保留最长连续行为，长度相同取最早开始的。

-- 步骤1：用 日期-行号 识别连续序列
with streak_groups as(
    select
        user_id,action,action_date
        ,to_days(action_date)-row_number() over(partition by user_id,action order by action_date) as grp  -- 分组标识
    from activity
)
-- 步骤2：聚合连续序列，并直接过滤掉长度<5的
,streaks as(
    select user_id,action
        ,count(*) as streak_length
        ,min(action_date) as start_date
        ,max(action_date) as end_date
    from streak_groups
    group by user_id,action,grp
    having count(*) >= 5
)
-- 步骤3：每个用户只保留最长的那条，最终排序
select
    user_id
    ,action
    ,streak_length
    ,start_date
    ,end_date
from(
    select *,row_number() over(partition by user_id order by streak_length DESC) as rn
    from streaks 
) t
where rn = 1
order by streak_length DESC,user_id ASC;

1. 用 日期-行号 识别连续序列
2. 保留长度大于等于5的，并聚合所需 长度、开始日期、结束日期字段
3. 再用 row_number 开窗，选出每个用户最长的

• 核心原理：to_days(日期) - 行号 = 分组标记grp

  • 同一连续区间内，这个差值恒定；日期断档后差值变化，自然拆分连续段。
  • grp是分组标识，grp相同表示是在同一个连续组里。

• 优化代码

  • 原 grp 逻辑：

    DATE_SUB(action_date, INTERVAL rn DAY) AS grp

    每次执行日期减法、生成 date 类型，存储开销大、对比慢；

    等价数字分组：

    TO_DAYS(action_date) - rn AS grp_key

    TO_DAYS 把日期转为纯数字，减法是整型运算，分组、哈希匹配速度更快。

• ​ – 可选前置：如果业务存在同一天多同action（本题不需要）

  • – SELECT DISTINCT user_id, action_date, action FROM activity

​

牛客

SQL160 国庆期间每类视频点赞量和转发量

with tmp as(
    -- 按日期和类别聚合，计算每天的点赞数和转发数
    select
        t2.tag
        ,date(t1.start_time) as dt
        ,sum(t1.if_like) as daily_likes
        ,sum(t1.if_retweet) as daily_retweets
    from tb_user_video_log t1
    join tb_video_info t2 on t1.video_id = t2.video_id
    group by 1,2
)
, tmp2 as(
    -- 使用窗口函数计算近7天的滑动窗口指标
    select *
        -- 近7天总点赞量（当前行及前6行）
        ,sum(daily_likes) over(partition by tag order by dt rows between 6 preceding and current row) as week_likes
        -- 近7天最大单日转发量
        ,max(daily_retweets) over(partition by tag order by dt rows between 6 preceding and current row) as max_retweets
    from tmp
)
-- 筛选国庆头3天，按视频类别降序、日期升序排序
select
    tag
    ,dt
    ,week_likes
    ,max_retweets
from tmp2
where dt >= '2021-10-01'
    and dt <= '2021-10-03'
order by tag DESC,dt ASC

这题感觉挺难的，但是和工作场景结合的很好。

本质：对每天计算近一周的数据汇总问题

题目框架
tb_user_video_log每条数据为一条观看记录，包含时间start_time，是否点赞if_like，是否转发if_retweet
tb_video_info存放视频本身数据，包括类别tag等，二者用video_id相关联

要求计算国庆头三天每类视频每天的近一周总点赞量和一周内最大单天转发量

步骤与难点

• 先计算每天每个类别的点赞量与转发量
• 运用 rows 6 preceding 计算对于每个不同日期来说，近一周的数据汇总
• 对于select语句中的窗口函数，会在select前先执行where中的日期限制，所以如果想要对于窗口函数汇总后的结果进行日期限制，不能把where的日期限制和窗口函数放在同一个结构中，否则只会执行限制后的日期汇总。

SQL窗口函数 | Kkkika