高频 SQL 50 题（基础版） 已完结

高频 SQL 50 题（基础版）
https://leetcode.cn/studyplan/sql-free-50/

• 查询
  • 可回收且低脂的产品
  • 寻找用户推荐人
  • 大的国家
  • 文章浏览 I
  • 无效的推文
• 连接
  • 使用唯一标识码替换员工ID
  • 产品销售分析 I
  • 进店却未进行过交易的顾客
  • 上升的温度
  • 每台机器的进程平均运行时间
  • 员工奖金
  • 学生们参加各科测试的次数
  • 至少有5名直接下属的经理
  • 确认率
• 聚合函数
  • 有趣的电影
  • 平均售价
  • 项目员工 I
  • 各赛事的用户注册率
  • 查询结果的质量和占比
• 排序和分组
  • 每位教师所教授的科目种类的数量
  • 查询近30天活跃用户数
  • 销售分析III
  • 超过 5 名学生的课
  • 求关注者的数量
  • 只出现一次的最大数字
  • 买下所有产品的客户
  • 每月交易 I
  • 即时食物配送 II
  • 游戏玩法分析 IV
• 高级查询和连接
  • 每位经理的下属员工数量
  • 员工的直属部门
  • 判断三角形
  • 连续出现的数字
  • 指定日期的产品价格
  • 最后一个能进入巴士的人
  • 按分类统计薪水
• 子查询
  • 上级经理已离职的公司员工
  • 换座位
  • 电影评分
  • 餐馆营业额变化增长
  • 好友申请 II ：谁有最多的好友
  • 2016年的投资
  • 部门工资前三高的所有员工
• 高级字符串函数 / 正则表达式 / 子句
  • 修复表中的名字
  • 患某种疾病的患者
  • 删除重复的电子邮箱
  • 第二高的薪水
  • 按日期分组销售产品
  • 列出指定时间段内所有的下单产品
  • 查找拥有有效邮箱的用户

查询

可回收且低脂的产品

select product_id from Products where low_fats = "Y" and recyclable = "Y";

寻找用户推荐人

提示

NULL 值判断需要使用 is null 或 is not null。

select name from Customer where referee_id is null or referee_id != 2;

大的国家

select name,population,`area` from World
where `area` >= 3000000 or population >= 25000000;

文章浏览 I

提示

去重：distinct [name]

排序：order by [name]

select distinct author_id id 
from Views where author_id = viewer_id
order by id;

无效的推文

提示

获取字符串长度（ASCII）：length()

获取字符串长度（更多字符）：char_length()

select tweet_id from Tweets
where length(content) > 15;

连接

使用唯一标识码替换员工ID

提示

保留 NULL 使用外连接，不保留 NULL 使用内连接。

使用 on 作为外连接的条件。

select unique_id, name
from Employees left join EmployeeUNI
on Employees.id = EmployeeUNI.id;

产品销售分析 I

多表查询

select product_name, `year`, price
from Sales, Product
where Sales.product_id = Product.product_id;

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

select customer_id, count(*) count_no_trans
from Visits
left join Transactions 
on Transactions.visit_id = Visits.visit_id
where transaction_id is null
group by customer_id;

上升的温度

提示

比较日期数据：datediff(date1, date2)

select w1.id
from Weather w1 join Weather w2
on datediff(w1.recordDate , w2.recordDate) = 1
where w1.temperature > w2.temperature;

每台机器的进程平均运行时间

select machine_id, round(avg(processing_time), 3) processing_time
from (
    select a1.machine_id, a1.process_id, a2.timestamp - a1.timestamp processing_time
    from (select * from Activity where activity_type = "start") a1
    join (select * from Activity where activity_type = "end") a2
    on a1.machine_id = a2.machine_id and a1.process_id = a2.process_id
) t
group by machine_id;

员工奖金

select name, bonus
from Employee e
left join Bonus b
on b.empId = e.empId
where bonus is null or bonus < 1000;

学生们参加各科测试的次数

提示

交叉联接（笛卡尔积）：cross join

对 NULL 值处理：ifnull([name], [defult_value])

select 
    student_id, e.student_name, s.subject_name, 
    ifnull(sum(e.subject_name = s.subject_name), 0) attended_exams
from (
    select ss.student_id, ss.student_name, ee.subject_name
    from Students ss
    left join Examinations ee
    on ee.student_id = ss.student_id
) e
cross join Subjects s
group by student_id, s.subject_name
order by student_id, s.subject_name;

至少有5名直接下属的经理

select e.name 
from (
    select managerId, count(*) cnt
    from Employee
    group by managerId
) t
join Employee e
on e.id = t.managerId
where t.managerId is not null and t.cnt >= 5;

确认率

select 
    c.user_id, 
    ifnull(round(sum(c.action = "confirmed") * 1. / count(*), 2), 0) confirmation_rate
from (
    select s.user_id, c.action
    from Confirmations c
    right join Signups s
    on c.user_id = s.user_id
) c
group by c.user_id;

聚合函数

有趣的电影

select *
from cinema
where description != "boring" && id % 2 = 1
order by rating desc;

平均售价

select t.product_id, ifnull(round(sum(t.total) * 1. / sum(units), 2), 0) average_price
from (
    select p.product_id, units , units * price total
    from UnitsSold u
    right join Prices p
    on u.product_id = p.product_id and datediff(u.purchase_date, p.start_date) >= 0 and datediff(u.purchase_date, p.end_date) <= 0
) t
group by t.product_id;

项目员工 I

select p.project_id ,round(avg(experience_years), 2) as average_years
from Project p
left join Employee e
on p.employee_id = e.employee_id
group by project_id

各赛事的用户注册率

select 
    contest_id, 
    round(count(*) * 100. / (select count(*) from Users), 2) percentage
from Register r
group by contest_id
order by percentage desc, contest_id;

查询结果的质量和占比

select 
    t.query_name, 
    round(avg(t.quality), 2) quality,
    round(sum(t.rating < 3) * 100. / count(*), 2) poor_query_percentage
from (
    select *, rating / position quality
    from Queries
) t
where query_name is not null
group by t.query_name;

排序和分组

每位教师所教授的科目种类的数量

去重

select t.teacher_id, count(distinct subject_id) cnt 
from Teacher t
group by t.teacher_id;

分组聚合

select t.teacher_id, count(*) cnt 
from (
    select teacher_id, subject_id
    from Teacher
    group by teacher_id, subject_id
) t
group by t.teacher_id;

查询近30天活跃用户数

select activity_date `day`, count(distinct user_id) active_users
from Activity
where 
    datediff("2019-07-27", activity_date) < 30 && datediff("2019-07-27", activity_date) >= 0
group by activity_date;

销售分析III

select p.product_id, p.product_name 
from (
    select p.*
    from Product p
    join Sales s
    where p.product_id = s.product_id
    group by product_id
) p
left join (
    select distinct product_id, sale_date
    from Sales
    where 
        datediff("2019-01-01", sale_date) > 0 
        or datediff(sale_date, "2019-03-31") > 0
) t
on p.product_id = t.product_id
where t.sale_date is null;

超过 5 名学生的课

select class
from (
    select class, count(*) cnt
    from Courses
    group by class
) t
where t.cnt >= 5;

求关注者的数量

select user_id, count(*) followers_count
from Followers
group by user_id
order by user_id;

只出现一次的最大数字

提示

聚合函数过滤使用 having。

select max(t.num) num
from ( 
    select *, count(*) cnt
    from MyNumbers
    group by num
    having cnt = 1
    order by cnt, num desc
) t;

买下所有产品的客户

select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(*) from Product);

每月交易 I

提示

日期格式化，将年月日格式化成年月：date_format(trans_date, '%Y-%m')

if 函数：if(条件, true返回值, false 返回值)

select 
    date_format(trans_date, '%Y-%m') month, 
    country, 
    count(*) trans_count,
    sum(state = "approved") approved_count,
    sum(amount) trans_total_amount,
    sum(if(state = "approved", amount, 0)) approved_total_amount
from Transactions
group by country, month;

即时食物配送 II

select 
    round(sum(t.min_date = t.customer_pref_delivery_date) * 100. / count(distinct t.customer_id), 2) immediate_percentage
from( 
    select 
        customer_id , min(order_date) min_date,
        min(customer_pref_delivery_date) customer_pref_delivery_date
    from Delivery
    where datediff(order_date, customer_pref_delivery_date) <= 0
    group by customer_id
) t

游戏玩法分析 IV

select 
    round(
        sum(event_date is not null) * 1. / count(t.player_id),
        2
    ) fraction
from (
    select player_id, min(event_date) date
    from Activity
    group by player_id
) t
left join Activity a
on 
    datediff(a.event_date, t.date) = 1 
    and a.player_id = t.player_id

高级查询和连接

每位经理的下属员工数量

select e2.employee_id, e2.name, count(*) reports_count, round(avg(e1.age), 0) average_age
from Employees e1
left join Employees e2
on e1.reports_to = e2.employee_id
where e2.employee_id is not null
group by e2.employee_id
order by employee_id

员工的直属部门

select 
    e1.employee_id, 
    ifnull(e2.department_id, e1.department_id) department_id
from (
    select * from Employee group by employee_id
) e1
left join (
    select *
    from Employee
    where primary_flag = "Y"
) e2
on e1.employee_id = e2.employee_id

判断三角形

select 
    *, 
    if((x + y > z) and (x + z > y) and (y + z > x), "Yes", "No") triangle
from Triangle;

连续出现的数字

select distinct l1.num ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where
    l1.id = l2.id - 1
    and l2.id = l3.id - 1
    and l1.num = l2.num
    and l2.num = l3.num

指定日期的产品价格

select distinct p.product_id, if(t.product_id is null, 10, p.new_price) price
from Products p
left join(
    select product_id, max(change_date) max_date
    from Products
    where datediff("2019-08-16", change_date) >= 0
    group by product_id
) t
on p.product_id is null or t.product_id = p.product_id
where t.max_date is null or t.max_date = p.change_date
order by price desc;

最后一个能进入巴士的人

提示

使用变量：创建临时表创建变量，使用 @k := v 赋值

select t.person_name
from (
    select person_name, @sum := @sum + weight weight
    from Queue , (select @sum := 0) tmp
    order by turn
) t
where weight <= 1000
order by weight desc
limit 1

按分类统计薪水

提示

使用 union 可以将多个单独查询的结果组合在一起。

union all 不会去重，union会去重

select 'Low Salary' category, sum(income < 20000) accounts_count
from Accounts
union
select 'Average Salary' category, sum(income <= 50000 and income >= 20000) accounts_count
from Accounts
union
select 'High Salary' category, sum(income > 50000) accounts_count
from Accounts

子查询

上级经理已离职的公司员工

select t.employee_id
from (
    select e1.employee_id, e2.name manager_name, e1.salary
    from (
        select * from Employees e where manager_id is not null
    ) e1
    left join Employees e2
    on e1.manager_id = e2.employee_id
) t
where t.salary < 30000 and t.manager_name is null
order by employee_id;

换座位

select *
from ((
    select ifnull(s2.id, s1.id) id, s1.student
    from Seat s1
    left join Seat s2
    on s2.id = s1.id + 1
    where s1.id % 2 = 1
) union (
    select ifnull(s2.id, s1.id) id, s1.student
    from Seat s1
    left join Seat s2
    on s2.id = s1.id - 1
    where s1.id % 2 = 0
)) t
order by t.id

电影评分

(
    select name results
    from MovieRating mr
    join Users u
    on u.user_id = mr.user_id
    group by mr.user_id
    order by count(*) desc, u.name
    limit 1
)
union all
(
    select title results
    from (
        select mr.movie_id, avg(mr.rating) avg_rating
        from MovieRating mr
        where date_format(mr.created_at, "%Y-%m") = "2020-02"
        group by mr.movie_id
    ) t
    left join Movies m
    on m.movie_id = t.movie_id
    order by avg_rating desc, title
    limit 1
)

餐馆营业额变化增长

with Cust as (
    select visited_on, sum(amount) amount
    from Customer
    group by visited_on
)

select 
    c.visited_on visited_on,
    sum(c1.amount) amount,
    round(sum(c1.amount) * 1. / 7 ,2) average_amount
from Cust c
cross join Cust c1
on datediff(c.visited_on, c1.visited_on) >= 0 and datediff(c.visited_on, c1.visited_on) < 7
group by c.visited_on
having count(*) = 7

好友申请 II ：谁有最多的好友

select id, count(id) num
from(
    select requester_id id
    from RequestAccepted
    union all
    select accepter_id id
    from RequestAccepted
) t
group by id
order by num desc
limit 1

2016年的投资

select 
    round(sum(tiv_2016), 2) tiv_2016
from Insurance i
where
    i.tiv_2015 in (select i1.tiv_2015 from Insurance i1 where i.pid != i1.pid)
    and concat(i.lat,',',i.lon) in (select concat(lat,',',lon) from Insurance group by lat,lon having count(*) < 2)

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

select 
    d.name Department,
    e.name Employee,
    e.salary Salary
from Employee e
left join Department d
on e.departmentId = d.id
where 
    (
        select count(distinct e1.salary)
        from Employee e1
        where e.departmentId = e1.departmentId and e1.salary > e.salary
    ) < 3

高级字符串函数 / 正则表达式 / 子句

修复表中的名字

提示

1. 字符串连接：concat(str1, str2)
2. 大小写转换：upper(str)、lower(str)
3. 从左边或右边截取子串：left(str, n)、right(str, n)
4. 获取字符串长度：length(str)

select 
    user_id,
    concat(upper(left(name, 1)), lower(right(name, length(name) - 1))) name
from Users
order by user_id;

患某种疾病的患者

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

删除重复的电子邮箱

where 不能直接引用包含聚合函数的列，需要中间表。

delete 
from Person
where id not in (
    select * from (
        select min(id) from Person group by email
    ) t
)

第二高的薪水

select max(salary) SecondHighestSalary
from Employee
where salary != (
    select max(salary) from Employee
);

按日期分组销售产品

提示

字符串拼接函数

group_concat(
    [distinct] 字段 
    [order by 排序字段] 
    [separator '分隔符']
)

select 
    sell_date, 
    count(distinct product) num_sold,
    group_concat(
        distinct product
        order by product
        separator ','
    ) products
from Activities
group by sell_date
order by sell_date

列出指定时间段内所有的下单产品

select product_name , sum(unit) unit
from Orders o, Products p
where 
    date_format(o.order_date,  "%Y-%m") = "2020-02"
    and o.product_id = p.product_id
group by o.product_id
having unit >= 100

查找拥有有效邮箱的用户

正则表达式

1. ^：表示字符串的开始。
2. $：表示字符串的结束。
3. \w：字符集

select *
from Users
where mail regexp '^[a-zA-Z][\\w.-]*\\@leetcode\\.com$'