題目選自leetcode 上的題庫
可能不一定都是最優(yōu)解瑟枫，答案僅供參考
每道題后面都應(yīng)相應(yīng)的難度等級赞别，如果沒時(shí)間做的話 可以在leetcode 按出題頻率刷題
祝大家面試取得好的成績
1468. 計(jì)算稅后工資
難度中等
SQL架構(gòu)
Salaries 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| company_id | int |
| employee_id | int |
| employee_name | varchar |
| salary | int |
+---------------+---------+
(company_id, employee_id) 是這個(gè)表的主鍵
這個(gè)表包括員工的company id, id, name 和 salary
寫一條查詢 SQL 來查找每個(gè)員工的稅后工資
每個(gè)公司的稅率計(jì)算依照以下規(guī)則
- 如果這個(gè)公司員工最高工資不到 1000 乔外，稅率為 0%
- 如果這個(gè)公司員工最高工資在 1000 到 10000 之間凤粗，稅率為 24%
- 如果這個(gè)公司員工最高工資大于 10000 考赛，稅率為 49%
按任意順序返回結(jié)果惕澎，稅后工資結(jié)果取整
結(jié)果表格式如下例所示：
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 | 1866 |
+------------+-------------+---------------+--------+
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 欲虚，其每個(gè)員工的稅率為 49%
對于公司 2 集灌，最高工資是 700 ，其每個(gè)員工稅率為 0%
對于公司 3 复哆，最高工資是 7777 欣喧，其每個(gè)員工稅率是 24%
稅后工資計(jì)算 = 工資 - ( 稅率 / 100）*工資
對于上述案例，Morninngcat 的稅后工資 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 梯找，取整為 5911
select company_id,employee_id , employee_name,
round(case when maxsalary<1000 then salary
when maxsalary<10000 then salary*(1-0.24)
else salary*(1-0.49) end ,0)salary
from(
select *,max(salary) over(partition by company_id ) maxsalary
from Salaries
)t1
1479. 周內(nèi)每天的銷售情況
難度困難
SQL架構(gòu)
表：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 的消費(fèi)者訂購的日期.
表：Items
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
+---------------------+---------+
item_id 是該表主鍵
item_name 是商品的名字
item_category 是商品的類別
你是企業(yè)主唆阿，想要獲得分類商品和周內(nèi)每天的銷售報(bào)告。
寫一個(gè)SQL語句锈锤，報(bào)告 周內(nèi)每天 每個(gè)商品類別下訂購了多少單位驯鳖。
返回結(jié)果表單 按商品類別排序 。
查詢結(jié)果格式如下例所示：
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個(gè)單位(10 + 10)
在周二(2020-06-02)，Book分類(ids: 1, 2)下阎姥，總共銷售了5個(gè)單位
在周三(2020-06-03)记舆，Phone分類(ids: 3, 4)下，總共銷售了5個(gè)單位
在周四(2020-06-04)呼巴，Phone分類(ids: 3, 4)下泽腮，總共銷售了1個(gè)單位
在周五(2020-06-05)，Book分類(ids: 1, 2)下衣赶，總共銷售了10個(gè)單位诊赊，Glasses分類(ids: 5)下，總共銷售了5個(gè)單位
在周六, 沒有商品銷售
在周天(2020-06-14, 2020-06-21)府瞄，Phone分類(ids: 3, 4)下碧磅，總共銷售了10個(gè)單位(5 + 5)
沒有銷售 T-Shirt 類別的商品
select item_category as category,
sum(case when num = 2 then quantity else 0 end) as Monday,
sum(case when num = 3 then quantity else 0 end) as Tuesday,
sum(case when num = 4 then quantity else 0 end) as Wednesday,
sum(case when num = 5 then quantity else 0 end) as Thursday,
sum(case when num = 6 then quantity else 0 end) as Friday,
sum(case when num = 7 then quantity else 0 end) as Saturday,
sum(case when num = 1 then quantity else 0 end) as Sunday
from
(select item_category, quantity,dayofweek(order_date) as num from
items i left join orders o
on i.item_id=o.item_id) t
group by item_category
order by item_category
1485. 按日期分組銷售產(chǎn)品
難度簡單
SQL架構(gòu)
表 Activities：
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表沒有主鍵，它可能包含重復(fù)項(xiàng)。
此表的每一行都包含產(chǎn)品名稱和在市場上銷售的日期鲸郊。
編寫一個(gè) SQL 查詢來查找每個(gè)日期敲街、銷售的不同產(chǎn)品的數(shù)量及其名稱。
每個(gè)日期的銷售產(chǎn)品名稱應(yīng)按詞典序排列严望。
返回按 sell_date 排序的結(jié)果表多艇。
查詢結(jié)果格式如下例所示。
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, count(distinct product) num_sold,
group_concat(distinct product order by product) products
from Activities
group by sell_date
行轉(zhuǎn)列
1495. 上月播放的兒童適宜電影
難度簡單
SQL架構(gòu)
表: TVProgram
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| program_date | date |
| content_id | int |
| channel | varchar |
+---------------+---------+
(program_date, content_id) 是該表主鍵.
該表包含電視上的節(jié)目信息.
content_id 是電視一些頻道上的節(jié)目的 id.
表: Content
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| content_id | varchar |
| title | varchar |
| Kids_content | enum |
| content_type | varchar |
+------------------+---------+
content_id 是該表主鍵.
Kids_content 是枚舉類型, 取值為('Y', 'N'), 其中:
'Y' 表示兒童適宜內(nèi)容, 而'N'表示兒童不宜內(nèi)容.
content_type 表示內(nèi)容的類型, 比如電影, 電視劇等.
寫一個(gè) SQL 語句, 報(bào)告在 2020 年 6 月份播放的兒童適宜電影的去重電影名.
返回的結(jié)果表單沒有順序要求.
查詢結(jié)果的格式如下例所示.
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 title
from TVProgram t left join Content c
on t.content_id = c.content_id
where Kids_content ='Y'
and date_format(program_date ,'%Y-%m')='2020-06'
and content_type='Movies'
LEFT()函數(shù)參見：https://www.begtut.com/sql/func-mysql-left.html
REGEXP語法參見：
https://www.cnblogs.com/timssd/p/5882742.html
https://www.cnblogs.com/zhaopanpan/p/10133224.html
DATE_FORMAT()函數(shù)參見：https://www.w3school.com.cn/sql/func_date_format.asp
EXTRACT()函數(shù)參見：https://www.runoob.com/sql/func-extract.html
DATEDIFF()函數(shù)參見：https://www.runoob.com/sql/func-datediff-mysql.html
YEAR()函數(shù)參見：https://blog.csdn.net/moakun/article/details/82528829
MONTH()函數(shù)參見：https://www.yiibai.com/mysql/month.html
1501. 可以放心投資的國家
難度中等
SQL架構(gòu)
表 Person:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| id | int |
| name | varchar |
| phone_number | varchar |
+----------------+---------+
id 是該表主鍵.
該表每一行包含一個(gè)人的名字和電話號碼.
電話號碼的格式是:'xxx-yyyyyyy', 其中xxx是國家碼(3個(gè)字符), yyyyyyy是電話號碼(7個(gè)字符), x和y都表示數(shù)字. 同時(shí), 國家碼和電話號碼都可以包含前導(dǎo)0.
表 Country:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| name | varchar |
| country_code | varchar |
+----------------+---------+
country_code是該表主鍵.
該表每一行包含國家名和國家碼. country_code的格式是'xxx', x是數(shù)字.
表 Calls:
+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id | int |
| callee_id | int |
| duration | int |
+-------------+------+
該表無主鍵, 可能包含重復(fù)行.
每一行包含呼叫方id, 被呼叫方id和以分鐘為單位的通話時(shí)長. caller_id != callee_id
一家電信公司想要投資新的國家. 該公司想要投資的國家是: 該國的平均通話時(shí)長要嚴(yán)格地大于全球平均通話時(shí)長.
寫一段 SQL, 找到所有該公司可以投資的國家.
返回的結(jié)果表沒有順序要求.
查詢的結(jié)果格式如下例所示.
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的平均通話時(shí)長是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
國家Israel的平均通話時(shí)長是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
國家Morocco的平均通話時(shí)長是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000
全球平均通話時(shí)長 = (2 * (33 + 3 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通話時(shí)長大于全球平均通話時(shí)長的國家, 也是唯一的推薦投資的國家.
笛卡爾積
select c2.name as country
from Calls c1,Person p,Country c2
where (p.id=c1.caller_id or p.id=c1.callee_id) and c2.country_code=left(p.phone_number,3)
group by c2.name
having avg(duration)>(select avg(duration) from Calls)
思路更清晰
with people_country as
(
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code
)
select country
from
(
select country, avg(duration) avgtime
from
(
select caller_id id, duration
from Calls
union all
select callee_id, duration
from Calls
) t left join people_country
using(id)
group by country
) temp
where avgtime >
(
select avg(duration) avgtime
from
(
select caller_id, duration
from Calls
union all
select callee_id, duration
from Calls
) t
)
1511. 消費(fèi)者下單頻率
難度簡單
SQL架構(gòu)
表: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| country | varchar |
+---------------+---------+
customer_id 是該表主鍵.
該表包含公司消費(fèi)者的信息.
表: Product
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| description | varchar |
| price | int |
+---------------+---------+
product_id 是該表主鍵.
該表包含公司產(chǎn)品的信息.
price 是本產(chǎn)品的花銷.
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_id | int |
| order_date | date |
| quantity | int |
+---------------+---------+
order_id 是該表主鍵.
該表包含消費(fèi)者下單的信息.
customer_id 是買了數(shù)量為"quantity", id為"product_id"產(chǎn)品的消費(fèi)者的 id.
Order_date 是訂單發(fā)貨的日期, 格式為('YYYY-MM-DD').
寫一個(gè) SQL 語句, 報(bào)告消費(fèi)者的 id 和名字, 其中消費(fèi)者在 2020 年 6 月和 7 月, 每月至少花費(fèi)了$100.
結(jié)果表無順序要求.
查詢結(jié)果格式如下例所示.
Customers
+--------------+-----------+-------------+
| customer_id | name | country |
+--------------+-----------+-------------+
| 1 | Winston | USA |
| 2 | Jonathan | Peru |
| 3 | Moustafa | Egypt |
+--------------+-----------+-------------+
Product
+--------------+-------------+-------------+
| product_id | description | price |
+--------------+-------------+-------------+
| 10 | LC Phone | 300 |
| 20 | LC T-Shirt | 10 |
| 30 | LC Book | 45 |
| 40 | LC Keychain | 2 |
+--------------+-------------+-------------+
Orders
+--------------+-------------+-------------+-------------+-----------+
| 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 |
+--------------+-------------+-------------+-------------+-----------+
Result 表:
+--------------+------------+
| customer_id | name |
+--------------+------------+
| 1 | Winston |
+--------------+------------+
Winston 在2020年6月花費(fèi)了$300(300 * 1), 在7月花費(fèi)了$100(10 * 1 + 45 * 2).
Jonathan 在2020年6月花費(fèi)了$600(300 * 2), 在7月花費(fèi)了$20(2 * 10).
Moustafa 在2020年6月花費(fèi)了$110 (10 * 2 + 45 * 2), 在7月花費(fèi)了$0.
select customer_id,name
from Customers
where customer_id in
(select customer_id
from
(select customer_id, month(order_date) as month , sum(quantity*price) as total
from Orders o left join Product p on o.product_id = p.product_id
where month(order_date) = 6 or month(order_date)=7
group by customer_id,month(order_date)
) as t1
where total >=100
group by customer_id
having count(*)>=2
)
1517. Find Users With Valid E-Mails
難度簡單
SQL架構(gòu)
Table: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
| mail | varchar |
+---------------+---------+
user_id is the primary key for this table.
This table contains information of the users signed up in a website. Some e-mails are invalid.
Write an SQL query to find the users who have valid emails.
A valid e-mail has a prefix name and a domain where:
-
The prefix name is a string that may contain letters (upper or lower case), digits, underscore
'_', period'.'and/or dash'-'. The prefix name must start with a letter. -
The domain is
'@leetcode.com'.
Return the result table in any order.
The query result format is in the following example.
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 |
+---------+-----------+-------------------------+
Result table:
+---------+-----------+-------------------------+
| user_id | name | mail |
+---------+-----------+-------------------------+
| 1 | Winston | winston@leetcode.com |
| 3 | Annabelle | bella-@leetcode.com |
| 4 | Sally | sally.come@leetcode.com |
+---------+-----------+-------------------------+
The mail of user 2 doesn't have a domain.
The mail of user 5 has # sign which is not allowed.
The mail of user 6 doesn't have leetcode domain.
The mail of user 7 starts with a period.
考察正則表達(dá)式的使用
SELECT *
FROM Users
WHERE mail REGEXP '^[a-zA-Z]+[\\w_\\.\\-]*@leetcode.com$'
ORDER BY user_id;
select * from Users
where mail regexp '^[a-zA-Z]+[a-zA-Z0-9_\\./\\-]{0,}@leetcode.com$'
order by user_id
坑點(diǎn)：
1、前綴可能是一個(gè)字母黎侈，比如“J@leetcode.com”察署，所以匹配非首字母外的前綴字符數(shù)量要用{0,}或*，不能用+峻汉。
2贴汪、題意要求：underscore '', period '.' and/or dash '-'，/沒加單引號休吠，不留神可能寫漏/扳埂。
3、后綴可能是“@leetcodeecom”瘤礁，所以要對“.”加轉(zhuǎn)義符號阳懂。
4、后綴可能是“@LEETCODE.COM”蔚携，默認(rèn)是不區(qū)分大小寫匹配希太，所以要加上“BINARY”區(qū)分大小寫克饶。
語法：
1酝蜒、https://www.cnblogs.com/timssd/p/5882742.html
2、https://www.cnblogs.com/zhaopanpan/p/10133224.html
3矾湃、"雙反斜杠+w"表示字母亡脑、數(shù)字、下劃線，相對"a-zA-Z0-9"的寫法更簡潔霉咨。
1527. Patients With a Condition
難度簡單
SQL架構(gòu)
Table: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id is the primary key for this table.
'conditions' contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.
Write an SQL query to report the patient_id, patient_name all conditions of patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix
Return the result table in any order.
The query result format is in the following example.
Patients
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | Daniel | YFEV COUGH |
| 2 | Alice | |
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
| 5 | Alain | DIAB201 |
+------------+--------------+--------------+
Result table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
+------------+--------------+--------------+
Bob and George both have a condition that starts with DIAB1.
select patient_id , patient_name ,conditions
from Patients
where conditions like '%DIAB1%'
1532. The Most Recent Three Orders
難度中等
SQL架構(gòu)
Table: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
+---------------+---------+
customer_id is the primary key for this table.
This table contains information about customers.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| customer_id | int |
| cost | int |
+---------------+---------+
order_id is the primary key for this table.
This table contains information about the orders made by customer_id.
Each customer has one order per day.
Write an SQL query to find the most recent 3 orders of each user. If a user ordered less than 3 orders return all of their orders.
Return the result table sorted by customer_name in ascending order and in case of a tie by the customer_id in ascending order. If there still a tie, order them by the order_date in descending order.
The query result format is in the following example:
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 has 4 orders, we discard the order of "2020-06-10" because it is the oldest order.
Annabelle has only 2 orders, we return them.
Jonathan has exactly 3 orders.
Marwan ordered only one time.
We sort the result table by customer_name in ascending order, by customer_id in ascending order and by order_date in descending order in case of a tie.
Follow-up:
Can you write a general solution for the most recent n orders?
select name customer_name ,customer_id,order_id,order_date
from (
select name ,o.customer_id,order_id,order_date ,rank()over(partition by o.customer_id order by order_date desc) rk
from Orders o left join Customers c
on o.customer_id=c.customer_id
)t1
where rk <=3
order by customer_name ,customer_id,order_date desc
1543. Fix Product Name Format
難度簡單
SQL架構(gòu)
Table: Sales
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| sale_id | int |
| product_name | varchar |
| sale_date | date |
+--------------+---------+
sale_id is the primary key for this table.
Each row of this table contains the product name and the date it was sold.
Since table Sales was filled manually in the year 2000, product_name may contain leading and/or trailing white spaces, also they are case-insensitive.
Write an SQL query to report
-
product_namein lowercase without leading or trailing white spaces. -
sale_datein the format('YYYY-MM') -
totalthe number of times the product was sold in this month.
Return the result table ordered by product_name in ascending order, in case of a tie order it by sale_date in ascending order.
The query result format is in the following example.
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 table:
+--------------+--------------+----------+
| product_name | sale_date | total |
+--------------+--------------+----------+
| lcphone | 2000-01 | 2 |
| lckeychain | 2000-02 | 2 |
| lcphone | 2000-02 | 1 |
| matryoshka | 2000-03 | 1 |
+--------------+--------------+----------+
In January, 2 LcPhones were sold, please note that the product names are not case sensitive and may contain spaces.
In Februery, 2 LCKeychains and 1 LCPhone were sold.
In March, 1 matryoshka was sold.
select trim(lower(product_name)) as product_name,
date_format(sale_date,'%Y-%m') as sale_date,
count(*) as total
from Sales
group by trim(lower(product_name)), date_format(sale_date,'%Y-%m')
order by product_name asc, sale_date asc
注意大小寫蛙紫、空格
1549. The Most Recent Orders for Each Product
難度中等
SQL架構(gòu)
Table: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
+---------------+---------+
customer_id is the primary key for this table.
This table contains information about the customers.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| customer_id | int |
| product_id | int |
+---------------+---------+
order_id is the primary key for this table.
This table contains information about the orders made by customer_id.
There will be no product ordered by the same user more than once in one day.
Table: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
| price | int |
+---------------+---------+
product_id is the primary key for this table.
This table contains information about the Products.
Write an SQL query to find the most recent order(s) of each product.
Return the result table sorted by product_name in ascending order and in case of a tie by the product_id in ascending order. If there still a tie, order them by the order_id in ascending order.
The query result format is in the following example:
Customers
+-------------+-----------+
| customer_id | name |
+-------------+-----------+
| 1 | Winston |
| 2 | Jonathan |
| 3 | Annabelle |
| 4 | Marwan |
| 5 | Khaled |
+-------------+-----------+
Orders
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1 | 2020-07-31 | 1 | 1 |
| 2 | 2020-07-30 | 2 | 2 |
| 3 | 2020-08-29 | 3 | 3 |
| 4 | 2020-07-29 | 4 | 1 |
| 5 | 2020-06-10 | 1 | 2 |
| 6 | 2020-08-01 | 2 | 1 |
| 7 | 2020-08-01 | 3 | 1 |
| 8 | 2020-08-03 | 1 | 2 |
| 9 | 2020-08-07 | 2 | 3 |
| 10 | 2020-07-15 | 1 | 2 |
+----------+------------+-------------+------------+
Products
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | keyboard | 120 |
| 2 | mouse | 80 |
| 3 | screen | 600 |
| 4 | hard disk | 450 |
+------------+--------------+-------+
Result table:
+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard | 1 | 6 | 2020-08-01 |
| keyboard | 1 | 7 | 2020-08-01 |
| mouse | 2 | 8 | 2020-08-03 |
| screen | 3 | 3 | 2020-08-29 |
+--------------+------------+----------+------------+
keyboard's most recent order is in 2020-08-01, it was ordered two times this day.
mouse's most recent order is in 2020-08-03, it was ordered only once this day.
screen's most recent order is in 2020-08-29, it was ordered only once this day.
The hard disk was never ordered and we don't include it in the result table.
select product_name,product_id,order_id,order_date
from
(
select product_name ,o.product_id ,order_id,order_date ,
rank() over(partition by o.product_id order by order_date desc) rk
from Orders o left join Products p
on o.product_id =p.product_id
)t1
where rk =1
order by product_name,product_id,order_id
