1907. 按分类统计薪水
表: Accounts
+-------------+------+ | 列名 | 类型 | +-------------+------+ | account_id | int | | income | int | +-------------+------+ 在 SQL 中,account_id 是这个表的主键。 每一行都包含一个银行帐户的月收入的信息。
查询每个工资类别的银行账户数量。 工资类别如下:
"Low Salary"
:所有工资 严格低于20000
美元。"Average Salary"
: 包含 范围内的所有工资[$20000, $50000]
。-
"High Salary"
:所有工资 严格大于50000
美元。
结果表 必须 包含所有三个类别。 如果某个类别中没有帐户,则报告 0
。
按 任意顺序 返回结果表。
示例 1:
输入: Accounts 表: +------------+--------+ | account_id | income | +------------+--------+ | 3 | 108939 | | 2 | 12747 | | 8 | 87709 | | 6 | 91796 | +------------+--------+ 输出: +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 1 | | Average Salary | 0 | | High Salary | 3 | +----------------+----------------+ 解释: 低薪: 有一个账户 2. 中等薪水: 没有. 高薪: 有三个账户,他们是 3, 6和 8.
select 'Low Salary' category, sum(income<20000) accounts_count
from Accounts
union all
select 'Average Salary' category, sum(income>=20000 and income <=50000) accounts_count
from Accounts
union all
select 'High Salary' category, sum(income>50000) accounts_count
from Accounts;
分别计算出各个级别的数量,然后通过union将各个查询连起来。
1978. 上级经理已离职的公司员工
表: Employees
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | manager_id | int | | salary | int | +-------------+----------+ 在 SQL 中,employee_id 是这个表的主键。 这个表包含了员工,他们的薪水和上级经理的id。 有一些员工没有上级经理(其 manager_id 是空值)。
查找这些员工的id,他们的薪水严格少于$30000
并且他们的上级经理已离职。当一个经理离开公司时,他们的信息需要从员工表中删除掉,但是表中的员工的manager_id
这一列还是设置的离职经理的id 。
返回的结果按照employee_id
从小到大排序。
示例:
输入: Employees table: +-------------+-----------+------------+--------+ | employee_id | name | manager_id | salary | +-------------+-----------+------------+--------+ | 3 | Mila | 9 | 60301 | | 12 | Antonella | null | 31000 | | 13 | Emery | null | 67084 | | 1 | Kalel | 11 | 21241 | | 9 | Mikaela | null | 50937 | | 11 | Joziah | 6 | 28485 | +-------------+-----------+------------+--------+ 输出: +-------------+ | employee_id | +-------------+ | 11 | +-------------+ 解释: 薪水少于 30000 美元的员工有 1 号(Kalel) 和 11号 (Joziah)。 Kalel 的上级经理是 11 号员工,他还在公司上班(他是 Joziah )。 Joziah 的上级经理是 6 号员工,他已经离职,因为员工表里面已经没有 6 号员工的信息了,它被删除了。
select employee_id
from employees
where salary<30000 and manager_id not in (select employee_id from employees)
order by employee_id;
manager_id 为 null 的时候 in 和 not in 都不符合条件。
626. 换座位
表: Seat
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | student | varchar | +-------------+---------+ id是该表的主键(唯一值)列。 该表的每一行都表示学生的姓名和 ID。 id 是一个连续的增量。
编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数,则最后一个学生的id不交换。
按 id
升序 返回结果表。
示例 1:
输入: Seat 表: +----+---------+ | id | student | +----+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----+---------+ 输出: +----+---------+ | id | student | +----+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +----+---------+ 解释: 请注意,如果学生人数为奇数,则不需要更换最后一名学生的座位。
select
(case
when mod(id,2)!=0 and counts != id then id+1
when mod(id,2)!=0 and counts = id then id
else id-1
end) as id,student from seat, (select count(id) as counts from seat) as t
order by id;
先查询出id的数量追加到原表中,然后搜索当id为奇数且不等于总数(即不是最后一个)时id+1,等于总数时为id,当id为偶数时id-1。
1341. 电影评分
表:Movies
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | title | varchar | +---------------+---------+ movie_id 是这个表的主键(具有唯一值的列)。 title 是电影的名字。
表:Users
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | name | varchar | +---------------+---------+ user_id 是表的主键(具有唯一值的列)。
表:MovieRating
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | user_id | int | | rating | int | | created_at | date | +---------------+---------+ (movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。 这个表包含用户在其评论中对电影的评分 rating 。 created_at 是用户的点评日期。
请你编写一个解决方案:
- 查找评论电影数量最多的用户名。如果出现平局,返回字典序较小的用户名。
- 查找在
February 2020
平均评分最高 的电影名称。如果出现平局,返回字典序较小的电影名称。
字典序 ,即按字母在字典中出现顺序对字符串排序,字典序较小则意味着排序靠前。
返回结果格式如下例所示。
示例 1:
输入: Movies 表: +-------------+--------------+ | movie_id | title | +-------------+--------------+ | 1 | Avengers | | 2 | Frozen 2 | | 3 | Joker | +-------------+--------------+ Users 表: +-------------+--------------+ | user_id | name | +-------------+--------------+ | 1 | Daniel | | 2 | Monica | | 3 | Maria | | 4 | James | +-------------+--------------+ MovieRating 表: +-------------+--------------+--------------+-------------+ | movie_id | user_id | rating | created_at | +-------------+--------------+--------------+-------------+ | 1 | 1 | 3 | 2020-01-12 | | 1 | 2 | 4 | 2020-02-11 | | 1 | 3 | 2 | 2020-02-12 | | 1 | 4 | 1 | 2020-01-01 | | 2 | 1 | 5 | 2020-02-17 | | 2 | 2 | 2 | 2020-02-01 | | 2 | 3 | 2 | 2020-03-01 | | 3 | 1 | 3 | 2020-02-22 | | 3 | 2 | 4 | 2020-02-25 | +-------------+--------------+--------------+-------------+ 输出: Result 表: +--------------+ | results | +--------------+ | Daniel | | Frozen 2 | +--------------+ 解释: Daniel 和 Monica 都点评了 3 部电影("Avengers", "Frozen 2" 和 "Joker") 但是 Daniel 字典序比较小。 Frozen 2 和 Joker 在 2 月的评分都是 3.5,但是 Frozen 2 的字典序比较小。
(select u.name as results from movierating mr left join users u on u.user_id = mr.user_id group by mr.user_id order by count(mr.user_id) desc, u.name limit 1)
union all
(select m.title as results from movierating mr left join movies m on mr.movie_id=m.movie_id where mr.created_at>='2020-02-01' and mr.created_at<'2020-03-01' group by mr.movie_id order by avg(mr.rating) desc, m.title limit 1)
1.分组搜索后排序用limit只取第一条后用union all合并(union如果有两条重复时合并会变成一条)。
1321. 餐馆营业额变化增长
表: Customer
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | visited_on | date | | amount | int | +---------------+---------+ 在 SQL 中,(customer_id, visited_on) 是该表的主键。 该表包含一家餐馆的顾客交易数据。 visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。 amount 是一个顾客某一天的消费总额。
你是餐馆的老板,现在你想分析一下可能的营业额变化增长(每天至少有一位顾客)。
计算以 7 天(某日期 + 该日期前的 6 天)为一个时间段的顾客消费平均值。average_amount
要 保留两位小数。
结果按 visited_on
升序排序。
示例 1:
输入: Customer 表: +-------------+--------------+--------------+-------------+ | customer_id | name | visited_on | amount | +-------------+--------------+--------------+-------------+ | 1 | Jhon | 2019-01-01 | 100 | | 2 | Daniel | 2019-01-02 | 110 | | 3 | Jade | 2019-01-03 | 120 | | 4 | Khaled | 2019-01-04 | 130 | | 5 | Winston | 2019-01-05 | 110 | | 6 | Elvis | 2019-01-06 | 140 | | 7 | Anna | 2019-01-07 | 150 | | 8 | Maria | 2019-01-08 | 80 | | 9 | Jaze | 2019-01-09 | 110 | | 1 | Jhon | 2019-01-10 | 130 | | 3 | Jade | 2019-01-10 | 150 | +-------------+--------------+--------------+-------------+ 输出: +--------------+--------------+----------------+ | visited_on | amount | average_amount | +--------------+--------------+----------------+ | 2019-01-07 | 860 | 122.86 | | 2019-01-08 | 840 | 120 | | 2019-01-09 | 840 | 120 | | 2019-01-10 | 1000 | 142.86 | +--------------+--------------+----------------+ 解释: 第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86 第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120 第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120 第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
1.自连接
select a.visited_on,sum(b.amount) as amount,round(sum(amount)/7,2) as average_amount
from (select distinct visited_on from customer) a join customer b
on datediff(a.visited_on,b.visited_on) between 0 and 6
where a.visited_on>=(select min(visited_on) from customer)+6
group by a.visited_on
order by a.visited_on;
- 根据题意需要先查出大于最小日期+6的日期(a表)。
- 然后和原表(b表)进行全连接,连接条件为与a表的日期和b表的日期相差在0到6。
- 最后进行查询,以a表的日期作为分组条件。
2. 窗口函数
SELECT DISTINCT visited_on,
sum_amount AS amount,
ROUND(sum_amount/7, 2) AS average_amount
FROM (
SELECT visited_on, SUM(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding ) AS sum_amount
FROM Customer) t
-- 最后手动地从第7天开始
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6
- 通过sum窗口函数计算出当前行日期前六天的总数,以visited_on作为分组条件。
- where函数筛选出大于最小日期6天的行。