赞
踩
以下题目均来自力扣
Stocks
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
+---------------+---------+
(stock_name, day) 是这张表的主键
operation 列使用的是一种枚举类型,包括:('Sell','Buy')
此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
保证股票的每次'Sell'操作前,都有相应的'Buy'操作。
编写一个SQL查询来报告每支股票的资本损益。
股票的资本损益是一次或多次买卖股票后的全部收益或损失。
以任意顺序返回结果即可。
SQL查询结果的格式如下例所示:
Stocks 表: +---------------+-----------+---------------+--------+ | stock_name | operation | operation_day | price | +---------------+-----------+---------------+--------+ | Leetcode | Buy | 1 | 1000 | | Corona Masks | Buy | 2 | 10 | | Leetcode | Sell | 5 | 9000 | | Handbags | Buy | 17 | 30000 | | Corona Masks | Sell | 3 | 1010 | | Corona Masks | Buy | 4 | 1000 | | Corona Masks | Sell | 5 | 500 | | Corona Masks | Buy | 6 | 1000 | | Handbags | Sell | 29 | 7000 | | Corona Masks | Sell | 10 | 10000 | +---------------+-----------+---------------+--------+ Result 表: +---------------+-------------------+ | stock_name | capital_gain_loss | +---------------+-------------------+ | Corona Masks | 9500 | | Leetcode | 8000 | | Handbags | -23000 | +---------------+-------------------+ Leetcode 股票在第一天以1000美元的价格买入,在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。 Handbags 股票在第17天以30000美元的价格买入,在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。 Corona Masks 股票在第1天以10美元的价格买入,在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买,在第5天以500美元的价格出售。最后,它在第6天以1000美元的价格被买走,在第10天以10000美元的价格被卖掉。资本损益是每次(’Buy'->'Sell')操作资本收益或损失的和=(1010-10)+(500-1000)+(10000-1000)=1000-500+9000=9500美元。
用户分组,分出这个用户买的总价钱和卖出的总价钱,然后通过转换为一行,进行相减就可以求出 with t1 as( select stock_name, operation, sum(price) p from Stocks group by stock_name,operation ), t2 as( select stock_name, min(case operation when 'buy' then p end) buy_price, min(case operation when 'Sell' then p end) Sell_price from t1 group by stock_name ) select stock_name, Sell_price-buy_price capital_gain_loss from t2 order by stock_name desc ;
Customers
表:
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。
Orders
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。
请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名( customer_id
和 customer_name
),我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id
排序。
查询结果如下例所示。
Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Daniel | | 2 | Diana | | 3 | Elizabeth | | 4 | Jhon | +-------------+---------------+ Orders table: +------------+--------------+---------------+ | order_id | customer_id | product_name | +------------+--------------+---------------+ | 10 | 1 | A | | 20 | 1 | B | | 30 | 1 | D | | 40 | 1 | C | | 50 | 2 | A | | 60 | 3 | A | | 70 | 3 | B | | 80 | 3 | D | | 90 | 4 | C | +------------+--------------+---------------+ Result table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 3 | Elizabeth | +-------------+---------------+ 只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ,却没有购买产品 C 。
先找购买了产品C的顾客,然后去除这顾客,再从剩下的顾客中找到购买了A和B产品的,分组去重数量等于2就可以了
with o_out_c as(
select * from Orders where customer_id not in (select customer_id from Orders where product_name='C')
),
tmp as (
select * from o_out_c where product_name in ('A','B')
)
select * from Customers
where customer_id in
(select customer_id from tmp group by customer_id having count(distinct product_name)=2)
表:Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表单主键。
name 是用户名字。
表:Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id 是该表单主键。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单,以 travelled_distance
降序排列 ,如果有两个或者更多的用户旅行了相同的距离, 那么再以 name
升序排列 。
查询结果格式如下例所示。
Users 表: +------+-----------+ | id | name | +------+-----------+ | 1 | Alice | | 2 | Bob | | 3 | Alex | | 4 | Donald | | 7 | Lee | | 13 | Jonathan | | 19 | Elvis | +------+-----------+ Rides 表: +------+----------+----------+ | id | user_id | distance | +------+----------+----------+ | 1 | 1 | 120 | | 2 | 2 | 317 | | 3 | 3 | 222 | | 4 | 7 | 100 | | 5 | 13 | 312 | | 6 | 19 | 50 | | 7 | 7 | 120 | | 8 | 19 | 400 | | 9 | 7 | 230 | +------+----------+----------+ Result 表: +----------+--------------------+ | name | travelled_distance | +----------+--------------------+ | Elvis | 450 | | Lee | 450 | | Bob | 317 | | Jonathan | 312 | | Alex | 222 | | Alice | 120 | | Donald | 0 | +----------+--------------------+ Elvis 和 Lee 旅行了 450 英里,Elvis 是排名靠前的旅行者,因为他的名字在字母表上的排序比 Lee 更小。 Bob, Jonathan, Alex 和 Alice 只有一次行程,我们只按此次行程的全部距离对他们排序。 Donald 没有任何行程, 他的旅行距离为 0。
每个旅行者,所以用户表所有人都应该在,这里使用左连接(left join),然后根据姓名进行分组求和,求和里面设置如果为null的为0,最后进行排序
select
name,
sum(ifnull(distance,0)) travelled_distance
from
Users u
left join
Rides r
on
u.id=r.user_id
group by
name
order by
travelled_distance desc,name asc
;
表: Student
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| student_id | int |
| student_name | varchar |
+---------------------+---------+
student_id 是该表主键.
student_name 学生名字.
表: Exam
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| exam_id | int |
| student_id | int |
| score | int |
+---------------+---------+
(exam_id, student_id) 是该表主键.
学生 student_id 在测验 exam_id 中得分为 score.
成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
写一个 SQL 语句,找出在 所有 测验中都处于中游的学生 (student_id, student_name)
。
不要返回从来没有参加过测验的学生。返回结果表按照 student_id
排序。
查询结果格式如下。
Student 表: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 1 | Daniel | | 2 | Jade | | 3 | Stella | | 4 | Jonathan | | 5 | Will | +-------------+---------------+ Exam 表: +------------+--------------+-----------+ | exam_id | student_id | score | +------------+--------------+-----------+ | 10 | 1 | 70 | | 10 | 2 | 80 | | 10 | 3 | 90 | | 20 | 1 | 80 | | 30 | 1 | 70 | | 30 | 3 | 80 | | 30 | 4 | 90 | | 40 | 1 | 60 | | 40 | 2 | 70 | | 40 | 4 | 80 | +------------+--------------+-----------+ Result 表: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 2 | Jade | +-------------+---------------+ 对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。 对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。 对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。 学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。 因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。 由此, 我们仅仅返回学生 2 的信息。
排序开窗,根据分数正着排一次,然后反着再排一次,再做一个标志转换,排名不为1的都为0 select exam_id, student_id, score, if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) dr, if(dense_rank() over(partition by exam_id order by score)=1,1,0) ar from Exam ; 连接学生表,然后对学生id求分组,条件为sum(dr)=0和sum(ar)=0的 select t.student_id, student_name from (select exam_id, student_id, score, if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) dr, if(dense_rank() over(partition by exam_id order by score)=1,1,0) ar from Exam) t join Student s on t.student_id=s.student_id group by t.student_id,student_name having sum(dr)=0 and sum(ar)=0 ;
表: NPV
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
| npv | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一笔存货的年份, id 和对应净现值的信息.
表: Queries
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一次查询所对应存货的 id 和年份的信息.
写一个 SQL, 找到 Queries 表中每一次查询的净现值.
结果表没有顺序要求.
查询结果的格式如下所示:
NPV 表: +------+--------+--------+ | id | year | npv | +------+--------+--------+ | 1 | 2018 | 100 | | 7 | 2020 | 30 | | 13 | 2019 | 40 | | 1 | 2019 | 113 | | 2 | 2008 | 121 | | 3 | 2009 | 12 | | 11 | 2020 | 99 | | 7 | 2019 | 0 | +------+--------+--------+ Queries 表: +------+--------+ | id | year | +------+--------+ | 1 | 2019 | | 2 | 2008 | | 3 | 2009 | | 7 | 2018 | | 7 | 2019 | | 7 | 2020 | | 13 | 2019 | +------+--------+ 结果表: +------+--------+--------+ | id | year | npv | +------+--------+--------+ | 1 | 2019 | 113 | | 2 | 2008 | 121 | | 3 | 2009 | 12 | | 7 | 2018 | 0 | | 7 | 2019 | 0 | | 7 | 2020 | 30 | | 13 | 2019 | 40 | +------+--------+--------+ (7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0. 所有其它查询的净现值都能在 NPV 表中找到.
左连接,判断null为0
select q.id,q.year,ifnull(npv,0) npv
from Queries q
left join NPV n
on q.id=n.id and q.year=n.year
表:Sessions
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| session_id | int |
| duration | int |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位
你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 “[0-5>”, “[5-10>”, “[10-15>” 和 “15 or more” (单位:分钟)的会话数量,并以此绘制柱状图。
写一个SQL查询来报告(访问时长区间,会话总数)。结果可用任何顺序呈现
下方为查询的输出格式:
Sessions 表: +-------------+---------------+ | session_id | duration | +-------------+---------------+ | 1 | 30 | | 2 | 199 | | 3 | 299 | | 4 | 580 | | 5 | 1000 | +-------------+---------------+ Result 表: +--------------+--------------+ | bin | total | +--------------+--------------+ | [0-5> | 3 | | [5-10> | 1 | | [10-15> | 0 | | 15 or more | 1 | +--------------+--------------+ 对于 session_id 1,2 和 3 ,它们的访问时间大于等于 0 分钟且小于 5 分钟。 对于 session_id 4,它的访问时间大于等于 5 分钟且小于 10 分钟。 没有会话的访问时间大于等于 10 分钟且小于 15 分钟。 对于 session_id 5, 它的访问时间大于等于 15 分钟。
select '[0-5>' as bin,count(duration) as total
from Sessions
where duration < 300
union
select '[5-10>' as bin,count(duration) as total
from Sessions
where duration >= 300 and duration < 600
union
select '[10-15>' as bin,count(duration) as total
from Sessions
where duration >= 600 and duration < 900
union
select '15 or more' as bin,count(duration) as total
from Sessions
where duration > 900
表 Variables
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| name | varchar |
| value | int |
+---------------+---------+
name 是该表主键.
该表包含了存储的变量及其对应的值.
表 Expressions
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| left_operand | varchar |
| operator | enum |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.
写一个 SQL 查询, 以计算表 Expressions
中的布尔表达式.
返回的结果表没有顺序要求.
查询结果格式如下例所示.
Variables 表: +------+-------+ | name | value | +------+-------+ | x | 66 | | y | 77 | +------+-------+ Expressions 表: +--------------+----------+---------------+ | left_operand | operator | right_operand | +--------------+----------+---------------+ | x | > | y | | x | < | y | | x | = | y | | y | > | x | | y | < | x | | x | = | x | +--------------+----------+---------------+ Result 表: +--------------+----------+---------------+-------+ | left_operand | operator | right_operand | value | +--------------+----------+---------------+-------+ | x | > | y | false | | x | < | y | true | | x | = | y | false | | y | > | x | true | | y | < | x | false | | x | = | x | true | +--------------+----------+---------------+-------+ 如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.
Expressions表与Variables表进行连接两次,然后使用case when then else end去判断即可 with tmp as( select left_operand,v1.value left_value,operator,right_operand,v2.value right_value from Expressions e left join Variables v1 on e.left_operand=v1.name left join Variables v2 on e.right_operand=v2.name ) select left_operand, operator, right_operand, case when operator='>' and left_value>right_value then 'true' when operator='<' and left_value<right_value then 'true' when operator='=' and left_value=right_value then 'true' else 'false' end as value from tmp
表: Sales
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| sale_date | date |
| fruit | enum |
| sold_num | int |
+---------------+---------+
(sale_date,fruit) 是该表主键.
该表包含了每一天中"苹果" 和 "桔子"的销售情况.
写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异.
返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date
排序.
查询结果表如下例所示:
Sales 表: +------------+------------+-------------+ | sale_date | fruit | sold_num | +------------+------------+-------------+ | 2020-05-01 | apples | 10 | | 2020-05-01 | oranges | 8 | | 2020-05-02 | apples | 15 | | 2020-05-02 | oranges | 15 | | 2020-05-03 | apples | 20 | | 2020-05-03 | oranges | 0 | | 2020-05-04 | apples | 15 | | 2020-05-04 | oranges | 16 | +------------+------------+-------------+ Result 表: +------------+--------------+ | sale_date | diff | +------------+--------------+ | 2020-05-01 | 2 | | 2020-05-02 | 0 | | 2020-05-03 | 20 | | 2020-05-04 | -1 | +------------+--------------+ 在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2). 在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0). 在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20). 在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).
select
s1.sale_date sale_date,
s1.sold_num-s2.sold_num diff # 求差即可
from
Sales s1
join
Sales s2
on
s1.sale_date=s2.sale_date # 自连接在一起,方便计算
where
s1.fruit='apples' and s2.fruit='oranges' # 让苹果和橘子在一行上
order by
sale_date
表 Accounts
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.
表 Logins
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
写一个 SQL 查询, 找到活跃用户的 id 和 name.
活跃用户是指那些至少连续 5 天登录账户的用户.
返回的结果表按照 id 排序.
结果表格式如下例所示
Accounts 表: +----+----------+ | id | name | +----+----------+ | 1 | Winston | | 7 | Jonathan | +----+----------+ Logins 表: +----+------------+ | id | login_date | +----+------------+ | 7 | 2020-05-30 | | 1 | 2020-05-30 | | 7 | 2020-05-31 | | 7 | 2020-06-01 | | 7 | 2020-06-02 | | 7 | 2020-06-02 | | 7 | 2020-06-03 | | 1 | 2020-06-07 | | 7 | 2020-06-10 | +----+------------+ Result 表: +----+----------+ | id | name | +----+----------+ | 7 | Jonathan | +----+----------+ id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户. id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.
# 思路: # 对每个用户和日期进行排序,然后用日期去减排名,最后对用户和相减后的日期进行分组,如果数量大于等于5的则是连续登录 with tmp as( select l.id,l.login_date,a.name from Logins l left join Accounts a on l.id=a.id # 两表连接,使得id和name在一起 group by l.id,l.login_date,a.name # 分组,为了使每个用户每天只有一次 ), tmp1 as( select id, login_date, name, rank() over(partition by id order by login_date) rk # 排序 from tmp ), tmp2 as( select id, name, login_date, date_add(login_date,interval -rk day) dt # 求差 from tmp1 ) select distinct id, # 去重 name from tmp2 group by id,dt # 分组id和日期 hvaing count(*)>=5 # 数量大于等于5的说明连续登录5天 order by id ;
表: Points
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| x_value | int |
| y_value | int |
+---------------+---------+
id 是该表主键
每个点都用二维坐标 (x_value, y_value) 表示
写一个 SQL 语句,报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
结果表中的每一行包含三列 (p1, p2, area)
如下:
p1
和 p2
是矩形两个对角的 id
area
表示请按照面积 area
大小降序排列;如果面积相同的话, 则按照 p1
升序排序;若仍相同,则按 p2
升序排列。
查询结果如下例所示:
Points 表: +----------+-------------+-------------+ | id | x_value | y_value | +----------+-------------+-------------+ | 1 | 2 | 7 | | 2 | 4 | 8 | | 3 | 2 | 10 | +----------+-------------+-------------+ Result 表: +----------+-------------+-------------+ | p1 | p2 | area | +----------+-------------+-------------+ | 2 | 3 | 4 | | 1 | 2 | 2 | +----------+-------------+-------------+ https://assets.leetcode.com/uploads/2021/03/12/rect.png p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |8-10| = 4 p1 = 1 且 p2 = 2 时, 面积等于 ||2-4| * |7-8| = 2 p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 面积等于 0
SELECT p1.id P1, p2.id P2, (ABS(p1.x_value - p2.x_value) * ABS(p1.y_value - p2.y_value)) AS AREA FROM Points p1 JOIN Points p2 ON p1.id < p2.id HAVING AREA <> 0 ORDER BY AREA DESC, P1, P2 ;
Salaries
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| company_id | int |
| employee_id | int |
| employee_name | varchar |
| salary | int |
+---------------+---------+
(company_id, employee_id) 是这个表的主键
这个表包括员工的company id, id, name 和 salary
写一条查询 SQL 来查找每个员工的税后工资
每个公司的税率计算依照以下规则
按任意顺序返回结果,税后工资结果取整
结果表格式如下例所示:
Salaries 表: +------------+-------------+---------------+--------+ | company_id | employee_id | employee_name | salary | +------------+-------------+---------------+--------+ | 1 | 1 | Tony | 2000 | | 1 | 2 | Pronub | 21300 | | 1 | 3 | Tyrrox | 10800 | | 2 | 1 | Pam | 300 | | 2 | 7 | Bassem | 450 | | 2 | 9 | Hermione | 700 | | 3 | 7 | Bocaben | 100 | | 3 | 2 | Ognjen | 2200 | | 3 | 13 | Nyancat | 3300 | | 3 | 15 | Morninngcat | 7777 | +------------+-------------+---------------+--------+ Result 表: +------------+-------------+---------------+--------+ | company_id | employee_id | employee_name | salary | +------------+-------------+---------------+--------+ | 1 | 1 | Tony | 1020 | | 1 | 2 | Pronub | 10863 | | 1 | 3 | Tyrrox | 5508 | | 2 | 1 | Pam | 300 | | 2 | 7 | Bassem | 450 | | 2 | 9 | Hermione | 700 | | 3 | 7 | Bocaben | 76 | | 3 | 2 | Ognjen | 1672 | | 3 | 13 | Nyancat | 2508 | | 3 | 15 | Morninngcat | 5911 | +------------+-------------+---------------+--------+ 对于公司 1 ,最高工资是 21300 ,其每个员工的税率为 49% 对于公司 2 ,最高工资是 700 ,其每个员工税率为 0% 对于公司 3 ,最高工资是 7777 ,其每个员工税率是 24% 税后工资计算 = 工资 - ( 税率 / 100)*工资 对于上述案例,Morninngcat 的税后工资 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 ,取整为 5911
with tmp as( # 临时表一,求出每个salary对应的税率 select company_id, employee_id, employee_name, salary, case when salary<1000 then 0 when salary>1000 and salary<=10000 then 0.24 when salary>10000 then 0.49 end as lv # 每个salary对应的税率 from Salaries ), tmp1 as( # 临时表二,求出每个公司中税率最高的 select company_id, employee_id, employee_name, salary, max(lv) over(partition by company_id) lv1 # 税率最高的 from tmp ) select company_id, employee_id, employee_name, round(salary-salary*lv1,0) salary # 取整计算税后工资 from tmp1 :
表:Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| order_date | date |
| item_id | varchar |
| quantity | int |
+---------------+---------+
(order_id, item_id) 是该表主键
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.
表:Items
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
+---------------------+---------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别
你是企业主,想要获得分类商品和周内每天的销售报告。
写一个SQL语句,报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。
查询结果格式如下例所示:
Orders 表: +------------+--------------+-------------+--------------+-------------+ | order_id | customer_id | order_date | item_id | quantity | +------------+--------------+-------------+--------------+-------------+ | 1 | 1 | 2020-06-01 | 1 | 10 | | 2 | 1 | 2020-06-08 | 2 | 10 | | 3 | 2 | 2020-06-02 | 1 | 5 | | 4 | 3 | 2020-06-03 | 3 | 5 | | 5 | 4 | 2020-06-04 | 4 | 1 | | 6 | 4 | 2020-06-05 | 5 | 5 | | 7 | 5 | 2020-06-05 | 1 | 10 | | 8 | 5 | 2020-06-14 | 4 | 5 | | 9 | 5 | 2020-06-21 | 3 | 5 | +------------+--------------+-------------+--------------+-------------+ Items 表: +------------+----------------+---------------+ | item_id | item_name | item_category | +------------+----------------+---------------+ | 1 | LC Alg. Book | Book | | 2 | LC DB. Book | Book | | 3 | LC SmarthPhone | Phone | | 4 | LC Phone 2020 | Phone | | 5 | LC SmartGlass | Glasses | | 6 | LC T-Shirt XL | T-Shirt | +------------+----------------+---------------+ Result 表: +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | Book | 20 | 5 | 0 | 0 | 10 | 0 | 0 | | Glasses | 0 | 0 | 0 | 0 | 5 | 0 | 0 | | Phone | 0 | 0 | 5 | 1 | 0 | 0 | 10 | | T-Shirt | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ 在周一(2020-06-01, 2020-06-08),Book分类(ids: 1, 2)下,总共销售了20个单位(10 + 10) 在周二(2020-06-02),Book分类(ids: 1, 2)下,总共销售了5个单位 在周三(2020-06-03),Phone分类(ids: 3, 4)下,总共销售了5个单位 在周四(2020-06-04),Phone分类(ids: 3, 4)下,总共销售了1个单位 在周五(2020-06-05),Book分类(ids: 1, 2)下,总共销售了10个单位,Glasses分类(ids: 5)下,总共销售了5个单位 在周六, 没有商品销售 在周天(2020-06-14, 2020-06-21),Phone分类(ids: 3, 4)下,总共销售了10个单位(5 + 5) 没有销售 T-Shirt 类别的商品
with tmp as( select i.item_category,o.item_id, dayname(order_date) as wk,quantity from Orders o right join Items i # 临时表,用orders表右连接items表,因为需要的是items里面的全部类别 on o.item_id=i.item_id # 顺便使用dayname函数求出日期是星期几 ) select item_category Category, sum(if(wk='Monday',quantity,0)) as Monday, # 分组要使用聚合函数,日期为周一的进行相加 sum(if(wk='Tuesday',quantity,0)) as Tuesday, # 分组要使用聚合函数,日期为周二的进行相加 sum(if(wk='Wednesday',quantity,0)) as Wednesday, # 分组要使用聚合函数,日期为周三的进行相加 sum(if(wk='Thursday',quantity,0)) as Thursday, # 分组要使用聚合函数,日期为周四的进行相加 sum(if(wk='Friday',quantity,0)) as Friday, # 分组要使用聚合函数,日期为周五的进行相加 sum(if(wk='Saturday',quantity,0)) as Saturday, # 分组要使用聚合函数,日期为周六的进行相加 sum(if(wk='Sunday',quantity,0)) as Sunday # 分组要使用聚合函数,日期为周天的进行相加 from tmp group by item_category # 按照要求对类别进行分组 order by Category # 按照要求对类别进行排序
# 日期函数
# 1、直接求日期为星期几 : dayname(date)
select dayname('2022-01-18')
Tuesday
# 2、求日期为星期几:dayofweek(date) 1代表周日,2代表周一。。。。依次类推
select dayofweek('2022-01-18')
3
# 3、求日期为星期几:weekday(date) 0代表周一,1代表周二。。。。依次类推
select weekday('2022-01-18')
1
表 Activities
:
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键,它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date
排序的结果表。
查询结果格式如下例所示。
Activities 表: +------------+-------------+ | sell_date | product | +------------+-------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+-------------+ Result 表: +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+ 对于2020-05-30,出售的物品是 (Headphone, Basketball, T-shirt),按词典序排列,并用逗号 ',' 分隔。 对于2020-06-01,出售的物品是 (Pencil, Bible),按词典序排列,并用逗号分隔。 对于2020-06-02,出售的物品是 (Mask),只需返回该物品名。
SELECT
sell_date AS 'sell_date',
COUNT(DISTINCT product) AS 'num_sold', # 会重复,去重
GROUP_CONCAT(DISTINCT product
ORDER BY product ASC #按照字典序排列,升序
SEPARATOR ',') #用','分隔
AS 'products' #组内拼接
FROM
Activities
GROUP BY
sell_date
ORDER BY
sell_date;
表: TVProgram
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| program_date | date |
| content_id | int |
| channel | varchar |
+---------------+---------+
(program_date, content_id) 是该表主键.
该表包含电视上的节目信息.
content_id 是电视一些频道上的节目的 id.
表: Content
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| content_id | varchar |
| title | varchar |
| Kids_content | enum |
| content_type | varchar |
+------------------+---------+
content_id 是该表主键.
Kids_content 是枚举类型, 取值为('Y', 'N'), 其中:
'Y' 表示儿童适宜内容, 而'N'表示儿童不宜内容.
content_type 表示内容的类型, 比如电影, 电视剧等.
写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名.
返回的结果表单没有顺序要求.
查询结果的格式如下例所示.
TVProgram 表: +--------------------+--------------+-------------+ | program_date | content_id | channel | +--------------------+--------------+-------------+ | 2020-06-10 08:00 | 1 | LC-Channel | | 2020-05-11 12:00 | 2 | LC-Channel | | 2020-05-12 12:00 | 3 | LC-Channel | | 2020-05-13 14:00 | 4 | Disney Ch | | 2020-06-18 14:00 | 4 | Disney Ch | | 2020-07-15 16:00 | 5 | Disney Ch | +--------------------+--------------+-------------+ Content 表: +------------+----------------+---------------+---------------+ | content_id | title | Kids_content | content_type | +------------+----------------+---------------+---------------+ | 1 | Leetcode Movie | N | Movies | | 2 | Alg. for Kids | Y | Series | | 3 | Database Sols | N | Series | | 4 | Aladdin | Y | Movies | | 5 | Cinderella | Y | Movies | +------------+----------------+---------------+---------------+ Result 表: +--------------+ | title | +--------------+ | Aladdin | +--------------+ "Leetcode Movie" 是儿童不宜的电影. "Alg. for Kids" 不是电影. "Database Sols" 不是电影 "Alladin" 是电影, 儿童适宜, 并且在 2020 年 6 月份播放. "Cinderella" 不在 2020 年 6 月份播放.
select distinct # 6、去重电影名 c.title from Content c left join # 1、要的是title,所以左连接 TVProgram t on c.content_id=t.content_id # 2、连接条件 where c.content_type='Movies' # 3、条件判断,是电影的 and c.kids_content='Y' # 4、条件判断,是适宜儿童的 and date_format(t.program_date,'%Y-%m')='2020-06' # 5、条件判断,是指定日期的 ;
表 Person
:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| id | int |
| name | varchar |
| phone_number | varchar |
+----------------+---------+
id 是该表主键.
该表每一行包含一个人的名字和电话号码.
电话号码的格式是:'xxx-yyyyyyy', 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.
表 Country
:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| name | varchar |
| country_code | varchar |
+----------------+---------+
country_code是该表主键.
该表每一行包含国家名和国家码. country_code的格式是'xxx', x是数字.
表 Calls
:
+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id | int |
| callee_id | int |
| duration | int |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id
一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.
写一段 SQL, 找到所有该公司可以投资的国家.
返回的结果表没有顺序要求.
查询的结果格式如下例所示
Person 表: +----+----------+--------------+ | id | name | phone_number | +----+----------+--------------+ | 3 | Jonathan | 051-1234567 | | 12 | Elvis | 051-7654321 | | 1 | Moncef | 212-1234567 | | 2 | Maroua | 212-6523651 | | 7 | Meir | 972-1234567 | | 9 | Rachel | 972-0011100 | +----+----------+--------------+ Country 表: +----------+--------------+ | name | country_code | +----------+--------------+ | Peru | 051 | | Israel | 972 | | Morocco | 212 | | Germany | 049 | | Ethiopia | 251 | +----------+--------------+ Calls 表: +-----------+-----------+----------+ | caller_id | callee_id | duration | +-----------+-----------+----------+ | 1 | 9 | 33 | | 2 | 9 | 4 | | 1 | 2 | 59 | | 3 | 12 | 102 | | 3 | 12 | 330 | | 12 | 3 | 5 | | 7 | 9 | 13 | | 7 | 1 | 3 | | 9 | 7 | 1 | | 1 | 7 | 7 | +-----------+-----------+----------+ Result 表: +----------+ | country | +----------+ | Peru | +----------+ 国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667 国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500 国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000 全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000 所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.
with tmp as ( # 临时表tmp select caller_id caller, duration from Calls union all # 自联合 select callee_id caller, duration from Calls ) select c.name country from a left join Person p on a.caller=p.id left join Country c on left(p.phone_number, 3)=c.country_code group by c.name having avg(a.duration) > (select avg(duration) from a) # 判断条件 ;
表: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| country | varchar |
+---------------+---------+
customer_id 是该表主键.
该表包含公司消费者的信息.
表: Product
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| description | varchar |
| price | int |
+---------------+---------+
product_id 是该表主键.
该表包含公司产品的信息.
price 是本产品的花销.
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_id | int |
| order_date | date |
| quantity | int |
+---------------+---------+
order_id 是该表主键.
该表包含消费者下单的信息.
customer_id 是买了数量为"quantity", id为"product_id"产品的消费者的 id.
Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').
写一个 SQL 查询,报告在 2020 年 6 月和 7 月 每个月至少花费 $100 的客户的 customer_id 和 customer_name 。
以任意顺序返回结果表.
查询结果格式如下例所示
示例 1:
输入: Customers table: +--------------+-----------+-------------+ | customer_id | name | country | +--------------+-----------+-------------+ | 1 | Winston | USA | | 2 | Jonathan | Peru | | 3 | Moustafa | Egypt | +--------------+-----------+-------------+ Product table: +--------------+-------------+-------------+ | product_id | description | price | +--------------+-------------+-------------+ | 10 | LC Phone | 300 | | 20 | LC T-Shirt | 10 | | 30 | LC Book | 45 | | 40 | LC Keychain | 2 | +--------------+-------------+-------------+ Orders table: +--------------+-------------+-------------+-------------+-----------+ | order_id | customer_id | product_id | order_date | quantity | +--------------+-------------+-------------+-------------+-----------+ | 1 | 1 | 10 | 2020-06-10 | 1 | | 2 | 1 | 20 | 2020-07-01 | 1 | | 3 | 1 | 30 | 2020-07-08 | 2 | | 4 | 2 | 10 | 2020-06-15 | 2 | | 5 | 2 | 40 | 2020-07-01 | 10 | | 6 | 3 | 20 | 2020-06-24 | 2 | | 7 | 3 | 30 | 2020-06-25 | 2 | | 9 | 3 | 30 | 2020-05-08 | 3 | +--------------+-------------+-------------+-------------+-----------+ 输出: +--------------+------------+ | customer_id | name | +--------------+------------+ | 1 | Winston | +--------------+------------+ 解释: Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2). Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10). Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0.
# Write your MySQL query statement below with tmp as( # 临时表一 select o.customer_id, date_format(o.order_date,'%Y-%m') as dt, c.name, o.quantity*p.price as p from Orders o left join Product p on o.product_id=p.product_id left join Customers c on o.customer_id=c.customer_id # 三表相连,取其需要的字段 where date_format(o.order_date,'%Y-%m')='2020-06' # 日期条件 or date_format(o.order_date,'%Y-%m')='2020-07'), # 日期条件 tmp1 as( # 临时表二 select customer_id, name, dt, sum(p) p # 总价格 from tmp group by customer_id,dt) # 分组求总价格 select customer_id, name from tmp1 group by customer_id having count(*)>=2 and sum(if(p>=100,0,1))=0 # 条件判断 ;
用户表: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
| mail | varchar |
+---------------+---------+
user_id (用户 ID)是该表的主键。
这个表包含用户在某网站上注册的信息。有些邮箱是无效的。
写一条 SQL 语句,查询拥有有效邮箱的用户。
有效的邮箱包含符合下列条件的前缀名和域名:
前缀名是包含字母(大写或小写)、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头。
域名是 ‘@leetcode.com’ 。
按任意顺序返回结果表。
查询格式如下所示:
Users +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 2 | Jonathan | jonathanisgreat | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | | 5 | Marwan | quarz#2020@leetcode.com | | 6 | David | david69@gmail.com | | 7 | Shapiro | .shapo@leetcode.com | +---------+-----------+-------------------------+ 结果表: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | +---------+-----------+-------------------------+ 2 号用户的邮箱没有域名。 5 号用户的邮箱包含非法字符 #。 6 号用户的邮箱的域名不是 leetcode。 7 号用户的邮箱以句点(.)开头。
select
*
from
Users
where
mail
regexp
'^[a-zA-Z][a-zA-Z0-9\\_\\.\\-]*@leetcode\\.com$' # 正则表达式,\\进行转移
;
患者信息表: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id (患者 ID)是该表的主键。
'conditions' (疾病)包含 0 个或以上的疾病代码,以空格分隔。
这个表包含医院中患者的信息。
写一条 SQL 语句,查询患有 I 类糖尿病的患者 ID (patient_id)、患者姓名(patient_name)以及其患有的所有疾病代码(conditions)。I 类糖尿病的代码总是包含前缀 DIAB1 。
按任意顺序返回结果表。
查询结果格式如下示例所示:
Patients +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 1 | Daniel | YFEV COUGH | | 2 | Alice | | | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | | 5 | Alain | DIAB201 | +------------+--------------+--------------+ 结果表: +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | +------------+--------------+--------------+ Bob 和 George 都患有代码以 DIAB1 开头的疾病。
select
*
from
patients
where
conditions
regexp
'^DIAB1| DIAB1' # 正则表达式,|或者的意思
;
表:Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
+---------------+---------+
customer_id 是该表主键
该表包含消费者的信息
表:Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| customer_id | int |
| cost | int |
+---------------+---------+
order_id 是该表主键
该表包含id为customer_id的消费者的订单信息
每一个消费者 每天一笔订单
写一个 SQL 语句,找到每个用户的最近三笔订单。如果用户的订单少于 3 笔,则返回他的全部订单。
返回的结果按照 customer_name 升序排列。如果排名有相同,则继续按照 customer_id 升序排列。如果排名还有相同,则继续按照 order_date 降序排列。
查询结果格式如下例所示:
Customers +-------------+-----------+ | customer_id | name | +-------------+-----------+ | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | +-------------+-----------+ Orders +----------+------------+-------------+------+ | order_id | order_date | customer_id | cost | +----------+------------+-------------+------+ | 1 | 2020-07-31 | 1 | 30 | | 2 | 2020-07-30 | 2 | 40 | | 3 | 2020-07-31 | 3 | 70 | | 4 | 2020-07-29 | 4 | 100 | | 5 | 2020-06-10 | 1 | 1010 | | 6 | 2020-08-01 | 2 | 102 | | 7 | 2020-08-01 | 3 | 111 | | 8 | 2020-08-03 | 1 | 99 | | 9 | 2020-08-07 | 2 | 32 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------+ Result table: +---------------+-------------+----------+------------+ | customer_name | customer_id | order_id | order_date | +---------------+-------------+----------+------------+ | Annabelle | 3 | 7 | 2020-08-01 | | Annabelle | 3 | 3 | 2020-07-31 | | Jonathan | 2 | 9 | 2020-08-07 | | Jonathan | 2 | 6 | 2020-08-01 | | Jonathan | 2 | 2 | 2020-07-30 | | Marwan | 4 | 4 | 2020-07-29 | | Winston | 1 | 8 | 2020-08-03 | | Winston | 1 | 1 | 2020-07-31 | | Winston | 1 | 10 | 2020-07-15 | +---------------+-------------+----------+------------+ Winston 有 4 笔订单, 排除了 "2020-06-10" 的订单, 因为它是最老的订单。 Annabelle 只有 2 笔订单, 全部返回。 Jonathan 恰好有 3 笔订单。 Marwan 只有 1 笔订单。 结果表我们按照 customer_name 升序排列,customer_id 升序排列,order_date 降序排列。
# Write your MySQL query statement below with tmp as( # 临时表 select c.name, o.customer_id, o.order_id, o.order_date, row_number() over(partition by o.customer_id order by o.order_date desc) rk #排序 from orders o left join customers c # 两表联合 on o.customer_id=c.customer_id # 连接条件 ) select name customer_name, customer_id, order_id, order_date from tmp where rk<4 # 条件,前三笔订单 order by name,customer_id,order_date desc # 排序条件
表:Sales
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| sale_id | int |
| product_name | varchar |
| sale_date | date |
+--------------+---------+
sale_id 是该表主键
该表的每一行包含了产品的名称及其销售日期
因为在 2000 年该表是手工填写的,product_name
可能包含前后空格,而且包含大小写。
写一个 SQL 语句报告每个月的销售情况:
product_name
是小写字母且不包含前后空格sale_date
格式为 ('YYYY-MM')
total
是产品在本月销售的次数返回结果以 product_name
升序 排列,如果有排名相同,再以 sale_date
升序 排列。
查询结果格式如下所示:
Sales 表: +------------+------------------+--------------+ | sale_id | product_name | sale_date | +------------+------------------+--------------+ | 1 | LCPHONE | 2000-01-16 | | 2 | LCPhone | 2000-01-17 | | 3 | LcPhOnE | 2000-02-18 | | 4 | LCKeyCHAiN | 2000-02-19 | | 5 | LCKeyChain | 2000-02-28 | | 6 | Matryoshka | 2000-03-31 | +------------+------------------+--------------+ Result 表: +--------------+--------------+----------+ | product_name | sale_date | total | +--------------+--------------+----------+ | lcphone | 2000-01 | 2 | | lckeychain | 2000-02 | 2 | | lcphone | 2000-02 | 1 | | matryoshka | 2000-03 | 1 | +--------------+--------------+----------+ 1 月份,卖了 2 个 LcPhones,请注意产品名称是小写的,中间可能包含空格 2 月份,卖了 2 个 LCKeychains 和 1 个 LCPhone 3 月份,卖了 1 个 matryoshka
# Write your MySQL query statement below with tmp as( # 临时表tmp select lower(trim(product_name)) product_name, # product_name去掉空格并且全部为小写 date_format(sale_date,'%Y-%m') sale_date # 日期进行转换 from sales ) select product_name, sale_date, count(*) total # 聚合函数求数量 from tmp group by product_name,sale_date # 根据product_name,sale_date进行分组 order by product_name,sale_date # 根据product_name,sale_date进行排序 ;
# 1、字符串去空格
# ①去掉左空格:ltrim(string)
# ②去掉右空格:rtrim(string)
# ③去掉左右空格:trim(string)
# 2、字母大小写
# ①字母小写:lower(string)
# ②字母大写:upper(string)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。