LeetCode 刷题
如果 select 语句同时包含有 group by, having，limit，order by
那么它们的顺序是：
- where（限制属性）
- group by（分组）
- having（筛选）
- order by（排序）
- limit（分页）【限制记录条数】
LeetCode 刷题
577. 员工奖金【左连接： A left join B on...】
左连接后是一个新的表，后可以 + where 语句
# Write your MySQL query statement below
select name, bonus from Employee left join Bonus
on Employee.empId = Bonus.empId
where bonus < 1000 or bonus is null;
544. 寻找用户推荐人
sql里面的不等于，不包含null
# Write your MySQL query statement below
select name from Customer
where referee_id != 2 or referee_id is null;
610. 判断三角线【条件判断】
IF
# Write your MySQL query statement below、
select x, y, z, IF(x + y > z and y + z > x and x + z > y, 'Yes', 'No') as triangle from Triangle
case...when...
# Write your MySQL query statement below、
select
x,
y,
z,
case
when x + y > z and y + z > x and z + x > y THEN 'Yes'
else 'No'
end as triangle
from Triangle;
619. 只出现一次的最大数字【当表格为空时，如何返回 null 值】
空表示表格没有任何输出，null 表示输出为 null
# Write your MySQL query statement below
select(select num from MyNumbers
group by num
having count(num) = 1
order by num desc
limit 1) num;
620. 有趣的电影
627. 变更性别 【case when then】
# Write your MySQL query statement below
update Salary set sex = CASE
when sex = 'm' then 'f'
when sex = 'f' then 'm'
end;
1050. 至少合作过3次的演员和导演
# Write your MySQL query statement below
select actor_id, director_id from ActorDirector
group by actor_id, director_id
having count(*) >= 3;
1075. 项目员工Ⅰ【avg 保留 2 位小数】
# Write your MySQL query statement below
select project_id, round(avg(experience_years), 2) average_years from Project, Employee
where Project.employee_id = Employee.employee_id
group by project_id
1084. 销售分析Ⅲ
# Write your MySQL query statement below
# 在这个时间内售出的商品数量 = 总商品数量
select distinct Product.product_id, product_name from Product, Sales
where Product.product_id = Sales.product_id
group by Product.product_id
# 不在这个日期范围内，就会转换为 null，这样就不会被 count 计数
having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*);
1179. 重新格式化部门表 【行转列】
case when 只会 匹配 数据库的第一个数据，如果不是它就返回空了 用sum 就可以去 数据里 的每个值都匹配一次 ，返回 匹配得上的值总和。因为这里的月份都是不同的，所以只会匹配到一个，sum就是遍历所有月份
# Write your MySQL query statement below
SELECT id,
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;
1211. 查询结果的质量和占比
# Write your MySQL query statement below
select query_name, Round(sum(rating / position) / count(*), 2) quality, Round(sum(case when rating < 3 then 1 else 0 end) / count(*) * 100, 2) poor_query_percentage
from Queries
group by query_name;
1251. 平均售价
特殊情况：没有被卖出的情形：
round(ifnull(sum(price * units) / sum(units), 0), 2)
# Write your MySQL query statement below
select Prices.product_id, round(ifnull(sum(price * units) / sum(units), 0), 2) average_price from Prices left join UnitsSold on purchase_date between start_date and end_date
and Prices.product_id = UnitsSold.product_id
group by product_id
1484 按日期分组销售产品【分组后，拼接 group_concat】
按行拼接 group_concat
- 去重：在列名前 + distincat
- 默认分割符为逗号 , 可以指定其它分隔符
SELECT GROUP_CONCAT(your_column SEPARATOR ', ') AS concatenated_rows
FROM your_table;
答案如下：
Apple, Banana, Orange
# Write your MySQL query statement below
select sell_date, count(distinct product) num_sold, group_concat(distinct product) products from Activities
group by sell_date
order by sell_date asc;
1517. 查找拥有有效邮箱的用户【REGXP：正则表达式】
注意：. 需要转义
如果不转义的话：表示匹配任意单个字符，除了换行符（\n）。它是一个通配符，可以匹配任何字符，包括字母、数字、标点符号等。
# Write your MySQL query statement below
select user_id, name, mail from Users
where mail REGEXP '^[a-zA-Z][\\w\\./-]*@leetcode\\.com$';
1527. 患某种疾病的患者【正则】
# Write your MySQL query statement below
select * from Patients
where conditions REGEXP ' DIAB1|^DIAB1'; # 1. 开头的 2. 中间的
1581. 进店却未进行过交易的顾客【左连接后，取 is null的部分】
# Write your MySQL query statement below
select customer_id, count(1) count_no_trans from Visits left join Transactions on
Visits.visit_id = Transactions.visit_id
where amount is null
group by customer_id
1633. 各赛事的用户注册率
不想连接的话，就 嵌套
# Write your MySQL query statement below
select contest_id, round(count(distinct Register.user_id) / (select count(*) from Users) * 100, 2) percentage from Register
group by contest_id
order by percentage desc, contest_id asc;
1661. 每台机器的进程平均运行时间
# Write your MySQL query statement below
select machine_id, round(sum(case
when activity_type = 'start' then -timestamp
else timestamp
end) / count(distinct process_id), 3) processing_time from Activity # 除以线程的个数
group by machine_id
1667. 修复表中的名字【类似 字符串操作 substring 下标从 1 开始】
- 截取字符串
- left
- substring
- 大小写转换：
- 小写：LOWER
- 大写：UPPER
注意点：
char_length() 和 length() 区别：【非 ASCII 编码】
- char_length()：字符数
- length()：字节数
# Write your MySQL query statement below
select user_id, concat(upper(left(name, 1)), lower(substring(name, 2))) name from Users
order by user_id;
1731. 每位经理的下属员工数量【自连接】
# Write your MySQL query statement below
select e2.employee_id, e2.name, count(*) reports_count, round(avg(e1.age), 0) average_age from Employees e1, Employees e2
where e1.reports_to = e2.employee_id
group by e1.reports_to
order by e2.employee_id
1280. 学生们参加各科测试的次数
- 所有学生 + 所有科目
select * from Students, Subjects
| student_id | student_name | subject_name |
|---|---|---|
| 1 | Alice | Programming |
| 1 | Alice | Physics |
| 1 | Alice | Math |
| 2 | Bob | Programming |
| 2 | Bob | Physics |
| 2 | Bob | Math |
| 13 | John | Programming |
| 13 | John | Physics |
| 13 | John | Math |
| 6 | Alex | Programming |
| 6 | Alex | Physics |
| 6 | Alex | Math |
注意：count 用法：
- count(*) 不会统计 Null 的行
- count(列名) 不会统计此列为 Null 的行
根据这个图片 ===> 可以根据 B.student_id 来进行计数
# Write your MySQL query statement below
# 该表：所有学生 + 所有科目
select A.student_id, A.student_name, A.subject_name, count(B.student_id) attended_exams from
(select * from Students, Subjects) A left join Examinations B on
A.student_id = B.student_id and A.subject_name = B.subject_name
group by A.student_id, A.subject_name
order by A.student_id, A.subject_name
1789. 员工的直属部门【Union】
# Write your MySQL query statement below
select employee_id, department_id from Employee
group by employee_id
having count(*) = 1
union
select employee_id, department_id from Employee
where primary_flag = 'Y'
1795. 每个产品在不同商店的价格【行列转换】
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面（重要）
# Write your MySQL query statement below
select product_id, 'store1' store, store1 price from Products # 想要把列转成行的话，用引号包起来，然后取别名，再 Union
where store1 is not null
union
select product_id, 'store2' store, store2 price from Products
where store2 is not null
union
select product_id, 'store3' store, store3 price from Products
where store3 is not null;
1873. 计算特殊奖金【mysql 判断相等只有一个等号 =】
# Write your MySQL query statement below
select employee_id, (case
when employee_id % 2 = 1 and left(name, 1) != 'M' then salary
else 0 end) bonus from Employees
order by employee_id;
1965. 丢失信息的雇员【union会去重，union all 不会去重】
# Write your MySQL query statement below
# 1. 雇员的 姓名 丢失了
# 2. 雇员的 薪水信息 丢失了
select Employees.employee_id employee_id from Employees left join Salaries on
Employees.employee_id = Salaries.employee_id
where salary is null # 薪水缺失
union
select Salaries.employee_id employee_id from Salaries left join Employees on
Employees.employee_id = Salaries.employee_id
where name is null # 名字缺失
order by employee_id
1511. 消费者下单频率
- 先按照 id 和 month 分组
select c.customer_id, month(order_date), sum(price * quantity) from Customers c, Product p, Orders o
where c.customer_id = o.customer_id and
o.product_id = p.product_id and year(order_date) = 2020 and (month(order_date) = 6 or month(order_date) = 7)
group by c.customer_id, month(order_date)
having sum(price * quantity) >= 100
| customer_id | name |
|---|---|
| 2 | Jonathan |
| 1 | Winston |
| 3 | Moustafa |
| 1 | Winston |
- 将当前查询作为临时表，查找临时表，按照 id 分组，count = 2 便是答案
# Write your MySQL query statement below
select customer_id, name from
(select c.customer_id, name from Customers c, Product p, Orders o
where c.customer_id = o.customer_id and
o.product_id = p.product_id and year(order_date) = 2020 and (month(order_date) = 6 or month(order_date) = 7)
group by c.customer_id, month(order_date)
having sum(price * quantity) >= 100) t
group by customer_id
having count(customer_id) = 2
603. 连续空余座位【连续可用】
# Write your MySQL query statement below
select distinct c1.seat_id seat_id from Cinema c1, Cinema c2
where c1.free = c2.free and c1.free = 1 and (c1.seat_id = c2.seat_id + 1 or c1.seat_id = c2.seat_id - 1)
order by seat_id;
613. 直线上的最近距离【2点之间距离，绝对值 abs()】
# Write your MySQL query statement below
select min(abs(p1.x - p2.x)) shortest from Point p1 left join Point p2 on
p1.x != p2.x
1303. 求团队人数【子查询】
# 子查询：先查出每个团队的人数
select team_id, count(1) size from Employee
group by team_id;
然后用团队编号将子查询和原表进行关联
select employee_id, size team_size from Employee e left join
(select team_id, count(1) size from Employee
group by team_id) temp
on e.team_id = temp.team_id;
512. 游戏玩法分析 Ⅱ【子查询】
(player_id, event_date)可以联合查询
# Write your MySQL query statement below
select player_id, device_id from Activity
where (player_id, event_date) in
(select player_id, min(event_date) from Activity
group by player_id);
1112. 每位学生的最高成绩
- 先用子查询查出 id <===> max_grade
- 用联合查询，找出符合条件的 min(course_id)
# Write your MySQL query statement below
select student_id, min(course_id) course_id, grade from Enrollments
where (student_id, grade) in
(select student_id, max(grade) grade from Enrollments # id <===> max_grade
group by student_id)
group by student_id
order by student_id
1270. 向公司 CEO 汇报工作的所有人
连续左连接
select e1.employee_id as employee_id from Employees e1
left join Employees e2 on e1.manager_id = e2.employee_id
left join Employees e3 on e2.manager_id = e3.employee_id
left join Employees e4 on e3.manager_id = e4.employee_id
where
e1.employee_id != 1 and # Boss 的 id = 1
(e2.employee_id = 1 or
e3.employee_id = 1 or
e4.employee_id = 1)
| id | id2 | id3 | id4 |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 3 | 3 | 3 | 3 |
| 2 | 1 | 1 | 1 |
| 4 | 1 | 1 | 1 |
| 7 | 2 | 2 | 2 |
| 8 | 3 | 3 | 3 |
| 9 | 3 | 3 | 3 |
| 77 | 1 | 1 | 1 |
1596. 每位顾客最经常订购的商品
select customer_id, product_id, product_name from
(select
customer_id,
product_id,
product_name,
rank() over(partition by customer_id order by num desc) rk from
(select customer_id, product_name, o.product_id, count(1) num from Orders o
left join Products p on o.product_id = p.product_id
group by customer_id, product_id) tmep) temp1
where rk = 1
1332. 广告效果【根据类型不同求和】
SELECT
ad_id,
IFNULL(ROUND(SUM(IF (action = 'Clicked', 1, 0)) / (SUM(IF (action = 'Clicked', 1, 0)) + SUM(IF (action = 'Viewed', 1, 0))) * 100, 2), 0) AS ctr
FROM
Ads
GROUP BY
ad_id
ORDER BY
ctr DESC, ad_id
中等
184. 部门工资最高的员工【in特殊用法，2个字段】
# Write your MySQL query statement below
select d.name Department, e.name Employee, salary Salary from Employee e, Department d
where e.departmentId = d.id and
(e.departmentId, salary) in (select departmentId, max(salary) salary from Employee group by departmentId); # 每个部门的最大值， 所以要 departmentId
608. 树节点【not in 与 null 的真实含义】
A not in B的原理是拿A表值与B表值做是否不等的比较, 也就是a != b. 在sql中, null是缺失未知值而不是空值 https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
当你判断任意值a != null时, 官方说, "You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL", 任何与null值的对比都将返回null. 因此返回结果为否,这点可以用代码 select if(1 = null, 'true', 'false')证实.
# Write your MySQL query statement below
select id, (case
when p_id is null then 'Root'
when id not in (select p_id from Tree where p_id is not null) then 'Leaf'
else 'Inner'
end) type from Tree;
626. 换座位
# 思路如下
# Write your MySQL query statement below
# 两两交换
# 如果学生的数量是奇数，则最后一个学生的id不交换。
select id, (select student from Seat where id = id + 1)student from Seat
where id % 2 = 1
