sql刷題筆記（六）

題目選自leetcode 上的題庫

可能不一定都是最優(yōu)解看成，答案僅供參考

每道題后面都應(yīng)相應(yīng)的難度等級，如果沒時間做的話可以在leetcode 按出題頻率刷題

祝大家面試取得好的成績

1350. 院系無效的學(xué)生

難度簡單

SQL架構(gòu)

院系表: Departments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是該表的主鍵
該表包含一所大學(xué)每個院系的 id 信息

學(xué)生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| department_id | int     |
+---------------+---------+
id 是該表的主鍵
該表包含一所大學(xué)每個學(xué)生的 id 和他/她就讀的院系信息

寫一條 SQL 語句以查詢那些所在院系不存在的學(xué)生的 id 和姓名

可以以任何順序返回結(jié)果

下面是返回結(jié)果格式的例子

Departments 表:
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
| 1    | Electrical Engineering   |
| 7    | Computer Engineering     |
| 13   | Bussiness Administration |
+------+--------------------------+

Students 表:
+------+----------+---------------+
| id   | name     | department_id |
+------+----------+---------------+
| 23   | Alice    | 1             |
| 1    | Bob      | 7             |
| 5    | Jennifer | 13            |
| 2    | John     | 14            |
| 4    | Jasmine  | 77            |
| 3    | Steve    | 74            |
| 6    | Luis     | 1             |
| 8    | Jonathan | 7             |
| 7    | Daiana   | 33            |
| 11   | Madelynn | 1             |
+------+----------+---------------+

結(jié)果表:
+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+

John, Daiana, Steve 和 Jasmine 所在的院系分別是 14, 33, 74 和 77跨嘉， 其中 14, 33, 74 和 77 并不存在于院系表

select id,name  
from Students 
where department_id not in 
(
    select id 
    from Departments
)

1355. 活動參與者

難度中等

SQL架構(gòu)

表: Friends

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| activity      | varchar |
+---------------+---------+
id 是朋友的 id 和該表的主鍵
name 是朋友的名字
activity 是朋友參加的活動的名字

表: Activities

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是該表的主鍵
name 是活動的名字

寫一條 SQL 查詢那些既沒有最多川慌，也沒有最少參與者的活動的名字

可以以任何順序返回結(jié)果，Activities 表的每項活動的參與者都來自 Friends 表

下面是查詢結(jié)果格式的例子：

Friends 表:
+------+--------------+---------------+
| id   | name         | activity      |
+------+--------------+---------------+
| 1    | Jonathan D.  | Eating        |
| 2    | Jade W.      | Singing       |
| 3    | Victor J.    | Singing       |
| 4    | Elvis Q.     | Eating        |
| 5    | Daniel A.    | Eating        |
| 6    | Bob B.       | Horse Riding  |
+------+--------------+---------------+

Activities 表:
+------+--------------+
| id   | name         |
+------+--------------+
| 1    | Eating       |
| 2    | Singing      |
| 3    | Horse Riding |
+------+--------------+

Result 表:
+--------------+
| activity     |
+--------------+
| Singing      |
+--------------+

Eating 活動有三個人參加, 是最多人參加的活動 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活動有一個人參加, 是最少人參加的活動 (Bob B.)
Singing 活動有兩個人參加 (Victor J. and Jade W.)

    select activity
    from (
        select activity,
        rank()over(order by cnt) rk1,
        rank()over(order by cnt desc) rk2
        from
        (
            select  activity  ,count(*) cnt
            from  Friends
            group by  activity 
        )t1
    )t2
    where rk1 !=1 and rk2 != 1

不需要關(guān)聯(lián) Activities表祠乃，因為至少有一人參加

1364. 顧客的可信聯(lián)系人數(shù)量

難度中等

SQL架構(gòu)

顧客表：Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
| email         | varchar |
+---------------+---------+
customer_id 是這張表的主鍵梦重。
此表的每一行包含了某在線商店顧客的姓名和電子郵件。

聯(lián)系方式表：Contacts

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | id      |
| contact_name  | varchar |
| contact_email | varchar |
+---------------+---------+
(user_id, contact_email) 是這張表的主鍵亮瓷。
此表的每一行表示編號為 user_id 的顧客的某位聯(lián)系人的姓名和電子郵件琴拧。
此表包含每位顧客的聯(lián)系人信息，但顧客的聯(lián)系人不一定存在于顧客表中寺庄。

發(fā)票表：Invoices

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| invoice_id   | int     |
| price        | int     |
| user_id      | int     |
+--------------+---------+
invoice_id 是這張表的主鍵艾蓝。
此表的每一行分別表示編號為 user_id 的顧客擁有有一張編號為 invoice_id、價格為 price 的發(fā)票斗塘。

為每張發(fā)票 invoice_id 編寫一個SQL查詢以查找以下內(nèi)容：

customer_name：與發(fā)票相關(guān)的顧客名稱赢织。
price：發(fā)票的價格。
contacts_cnt：該顧客的聯(lián)系人數(shù)量馍盟。
trusted_contacts_cnt：可信聯(lián)系人的數(shù)量：既是該顧客的聯(lián)系人又是商店顧客的聯(lián)系人數(shù)量（即：可信聯(lián)系人的電子郵件存在于客戶表中）于置。

將查詢的結(jié)果按照 invoice_id 排序。

查詢結(jié)果的格式如下例所示：

Customers table:
+-------------+---------------+--------------------+
| customer_id | customer_name | email              |
+-------------+---------------+--------------------+
| 1           | Alice         | alice@leetcode.com |
| 2           | Bob           | bob@leetcode.com   |
| 13          | John          | john@leetcode.com  |
| 6           | Alex          | alex@leetcode.com  |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user_id     | contact_name | contact_email      |
+-------------+--------------+--------------------+
| 1           | Bob          | bob@leetcode.com   |
| 1           | John         | john@leetcode.com  |
| 1           | Jal          | jal@leetcode.com   |
| 2           | Omar         | omar@leetcode.com  |
| 2           | Meir         | meir@leetcode.com  |
| 6           | Alice        | alice@leetcode.com |
+-------------+--------------+--------------------+
Invoices table:
+------------+-------+---------+
| invoice_id | price | user_id |
+------------+-------+---------+
| 77         | 100   | 1       |
| 88         | 200   | 1       |
| 99         | 300   | 2       |
| 66         | 400   | 2       |
| 55         | 500   | 13      |
| 44         | 60    | 6       |
+------------+-------+---------+
Result table:
+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44         | Alex          | 60    | 1            | 1                    |
| 55         | John          | 500   | 0            | 0                    |
| 66         | Bob           | 400   | 2            | 0                    |
| 77         | Alice         | 100   | 3            | 2                    |
| 88         | Alice         | 200   | 3            | 2                    |
| 99         | Bob           | 300   | 2            | 0                    |
+------------+---------------+-------+--------------+----------------------+
Alice 有三位聯(lián)系人贞岭，其中兩位(Bob 和 John)是可信聯(lián)系人八毯。
Bob 有兩位聯(lián)系人, 他們中的任何一位都不是可信聯(lián)系人搓侄。
Alex 只有一位聯(lián)系人(Alice)，并是一位可信聯(lián)系人话速。
John 沒有任何聯(lián)系人讶踪。

select invoice_id ,customer_name,price,ifnull(cnt,0) contacts_cnt,ifnull(bc,0) trusted_contacts_cnt 
from Invoices i
left join (
select user_id ,count(*) cnt
from Contacts
group by user_id
) t1
on i.user_id=t1.user_id
left join (
select  user_id ,count(*) bc
from Contacts
    where contact_name in
    (
        select customer_name
        from Customers
    )
group by user_id 
)t2
on i.user_id = t2.user_id
left join Customers c
on i.user_id= c.customer_id
order by invoice_id

就是麻煩點各種join

1369. 獲取最近第二次的活動

難度困難

SQL架構(gòu)

表: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
該表不包含主鍵
該表包含每個用戶在一段時間內(nèi)進(jìn)行的活動的信息
名為 username 的用戶在 startDate 到 endDate 日內(nèi)有一次活動

寫一條SQL查詢展示每一位用戶 最近第二次 的活動

如果用戶僅有一次活動，返回該活動

一個用戶不能同時進(jìn)行超過一項活動泊交，以任意順序返回結(jié)果

下面是查詢結(jié)果格式的例子：

UserActivity 表:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Travel       | 2020-02-12  | 2020-02-20  |
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Alice      | Travel       | 2020-02-24  | 2020-02-28  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+

Result 表:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+

Alice 最近第二次的活動是從 2020-02-24 到 2020-02-28 的旅行, 在此之前的 2020-02-21 到 2020-02-23 她進(jìn)行了舞蹈
Bob 只有一條記錄乳讥，我們就取這條記錄

select username, activity ,startDate,endDate 
from 
(
select username, activity ,startDate,endDate ,
    rank()over(partition by username order by startDate desc) rk,
    lag( startDate ,1,null)over(partition by username order by startDate ) lg
from UserActivity
)t1
where rk=2 or  (rk = 1 &&  lg is null)

1378. 使用唯一標(biāo)識碼替換員工ID

難度簡單

SQL架構(gòu)

Employees 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是這張表的主鍵。
這張表的每一行分別代表了某公司其中一位員工的名字和 ID 廓俭。

EmployeeUNI 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+
(id, unique_id) 是這張表的主鍵云石。
這張表的每一行包含了該公司某位員工的 ID 和他的唯一標(biāo)識碼（unique ID）。

寫一段SQL查詢來展示每位用戶的 唯一標(biāo)識碼（unique ID ）研乒；如果某位員工沒有唯一標(biāo)識碼汹忠，使用 null 填充即可。

你可以以任意順序返回結(jié)果表雹熬。

查詢結(jié)果的格式如下例所示：

Employees table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+

EmployeeUNI table:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+

EmployeeUNI table:
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+

Alice and Bob 沒有唯一標(biāo)識碼, 因此我們使用 null 替代宽菜。
Meir 的唯一標(biāo)識碼是 2 。
Winston 的唯一標(biāo)識碼是 3 橄唬。
Jonathan 唯一標(biāo)識碼是 1 赋焕。

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

1384. 按年度列出銷售總額

難度困難

SQL架構(gòu)

Product 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id 是這張表的主鍵。
product_name 是產(chǎn)品的名稱仰楚。

Sales 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | varchar |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id 是這張表的主鍵。
period_start 和 period_end 是該產(chǎn)品銷售期的起始日期和結(jié)束日期犬庇，且這兩個日期包含在銷售期內(nèi)僧界。
average_daily_sales 列存儲銷售期內(nèi)該產(chǎn)品的日平均銷售額。

編寫一段SQL查詢每個產(chǎn)品每年的總銷售額臭挽，并包含 product_id, product_name 以及 report_year 等信息捂襟。

銷售年份的日期介于 2018 年到 2020 年之間。你返回的結(jié)果需要按 product_id 和 report_year 排序欢峰。

查詢結(jié)果格式如下例所示：

Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1          | LC Phone     |
| 2          | LC T-Shirt   |
| 3          | LC Keychain  |
+------------+--------------+

Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end  | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1          | 2019-01-25   | 2019-02-28  | 100                 |
| 2          | 2018-12-01   | 2020-01-01  | 10                  |
| 3          | 2019-12-01   | 2020-01-31  | 1                   |
+------------+--------------+-------------+---------------------+

Result table:
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+
LC Phone 在 2019-01-25 至 2019-02-28 期間銷售葬荷，該產(chǎn)品銷售時間總計35天。銷售總額 35*100 = 3500纽帖。
LC T-shirt 在 2018-12-01 至 2020-01-01 期間銷售宠漩，該產(chǎn)品在2018年、2019年懊直、2020年的銷售時間分別是31天扒吁、365天、1天室囊，2018年雕崩、2019年魁索、2020年的銷售總額分別是31*10=310、365*10=3650盼铁、1*10=10粗蔚。
LC Keychain 在 2019-12-01 至 2020-01-31 期間銷售，該產(chǎn)品在2019年饶火、2020年的銷售時間分別是：31天支鸡、31天，2019年趁窃、2020年的銷售總額分別是31*1=31牧挣、31*1=31。

(
 select Sales.product_id, product_name, '2018' as 'report_year', if(period_start<'2019-01-01', (datediff(if(period_end<'2019-01-01', period_end, date('2018-12-31')), if(period_start>='2018-01-01', period_start, date('2018-01-01')))+1)*average_daily_sales, 0) as total_amount
from Sales  
join Product on Sales.product_id = Product.product_id 
having  total_amount>0
)
union(
select Sales.product_id, product_name, '2019' as 'report_year', if( period_start<'2020-01-01', (datediff(if(period_end<'2020-01-01', period_end, date('2019-12-31')), if(period_start>='2019-01-01', period_start, date('2019-01-01')))+1)*average_daily_sales , 0) as total_amount
from Sales  
join Product on (Sales.product_id = Product.product_id )
having  total_amount>0
)
union(
select Sales.product_id, product_name, '2020' as 'report_year', (datediff(if(period_end<'2021-01-01', period_end, date('2020-12-31')), if(period_start>='2020-01-01', period_start, date('2020-01-01')))+1)*average_daily_sales as total_amount
from Sales  
join Product  on (Sales.product_id = Product.product_id)
having total_amount>0
) 
order by product_id, report_year

各個年份進(jìn)行union,就是年份判斷的時候麻煩些

1393. 股票的資本損益

難度中等

SQL架構(gòu)

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'操作前，都有相應(yīng)的'Buy'操作刨摩。

編寫一個SQL查詢來報告每支股票的資本損益寺晌。

股票的資本損益是一次或多次買賣股票后的全部收益或損失。

以任意順序返回結(jié)果即可澡刹。

SQL查詢結(jié)果的格式如下例所示：

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美元。

select stock_name,sell-buy capital_gain_loss
from(
select stock_name ,
       sum(if(operation='Buy', price,0))over(partition by stock_name ) buy,
       sum(if(operation='Sell',price,0))over(partition by stock_name) sell
from Stocks s
)t1
group by stock_name,buy,sell

1398. 購買了產(chǎn)品A和產(chǎn)品B卻沒有購買產(chǎn)品C的顧客

難度中等

SQL架構(gòu)

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" 產(chǎn)品顧客的id案怯。

請你設(shè)計 SQL 查詢來報告購買了產(chǎn)品 A 和產(chǎn)品 B 卻沒有購買產(chǎn)品 C 的顧客的 ID 和姓名（ customer_id 和 customer_name ）君旦，我們將基于此結(jié)果為他們推薦產(chǎn)品 C 。
您返回的查詢結(jié)果需要按照 customer_id 排序。

查詢結(jié)果如下例所示金砍。

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 的顧客購買了產(chǎn)品 A 和產(chǎn)品 B 局蚀，卻沒有購買產(chǎn)品 C 。

select  o.customer_id, customer_name 
from Orders o left join  Customers c
on o.customer_id=c.customer_id
group by customer_id
having sum(product_name ='A')>=1 and sum(product_name='B')>=1 and sum(product_name='C')=0

1407. 排名靠前的旅行者

難度簡單

SQL架構(gòu)

表單: 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 , 報告每個用戶的旅行距離.

返回的結(jié)果表單, 以 travelled_distance 降序排列, 如果有兩個或者更多的用戶旅行了相同的距離, 那么再以 name 升序排列.

查詢結(jié)果格式, 如下例所示.

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.

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

1412. 查找成績處于中游的學(xué)生

難度困難

SQL架構(gòu)

表: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是該表主鍵.
student_name 學(xué)生名字.

表: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是該表主鍵.
學(xué)生 student_id 在測驗 exam_id 中得分為 score.

成績處于中游的學(xué)生是指至少參加了一次測驗, 且得分既不是最高分也不是最低分的學(xué)生恕稠。

寫一個 SQL 語句琅绅，找出在所有測驗中都處于中游的學(xué)生 (student_id, student_name)。

不要返回從來沒有參加過測驗的學(xué)生鹅巍。返回結(jié)果表按照 student_id 排序千扶。

查詢結(jié)果格式如下。

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: 學(xué)生 1 和 3 分別獲得了最低分和最高分骆捧。
對于測驗 2: 學(xué)生 1 既獲得了最高分, 也獲得了最低分澎羞。
對于測驗 3 和 4: 學(xué)生 1 和 4 分別獲得了最低分和最高分。
學(xué)生 2 和 5 沒有在任一場測驗中獲得了最高分或者最低分敛苇。
因為學(xué)生 5 從來沒有參加過任何測驗, 所以他被排除于結(jié)果表妆绞。
由此, 我們僅僅返回學(xué)生 2 的信息。

select e.student_id,student_name
from Exam e left join Student s
on e.student_id=s.student_id
where e.student_id not in(
    select student_id
    from(
        select student_id,rank() over(partition by exam_id order by score desc) rkmax, rank() over(partition by exam_id order by score ) rkmin
        from Exam 
    )t1
    where rkmax = 1 or rkmin =1
)
group by e.student_id,student_name
order by e.student_id

1421. 凈現(xiàn)值查詢

難度中等

SQL架構(gòu)

表: NPV

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
| npv           | int     |
+---------------+---------+
(id, year) 是該表主鍵.
該表有每一筆存貨的年份, id 和對應(yīng)凈現(xiàn)值的信息.

表: Queries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
+---------------+---------+
(id, year) 是該表主鍵.
該表有每一次查詢所對應(yīng)存貨的 id 和年份的信息.

寫一個 SQL, 找到 Queries 表中每一次查詢的凈現(xiàn)值.

結(jié)果表沒有順序要求.

查詢結(jié)果的格式如下所示:

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   |
+------+--------+

結(jié)果表:
+------+--------+--------+
| 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)的凈現(xiàn)值不在 NPV 表中, 我們把它看作是 0.
所有其它查詢的凈現(xiàn)值都能在 NPV 表中找到.

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

npv 凈現(xiàn)值概念了解下

1435. 制作會話柱狀圖

難度簡單

SQL架構(gòu)

表：Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id 是該表主鍵
duration 是用戶訪問應(yīng)用的時間, 以秒為單位

你想知道用戶在你的 app 上的訪問時長情況枫攀。因此決定統(tǒng)計訪問時長區(qū)間分別為 "[0-5>", "[5-10>", "[10-15>" 和 "15 or more" （單位：分鐘）的會話數(shù)量括饶，并以此繪制柱狀圖。

寫一個SQL查詢來報告（訪問時長區(qū)間来涨，會話總數(shù)）图焰。結(jié)果可用任何順序呈現(xiàn)。

下方為查詢的輸出格式：

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 分鐘颗味。

Union

select '[0-5>' as bin, count(*) as total from Sessions where duration/60>=0 and duration/60<5
union
select '[5-10>' as bin, count(*) as total from Sessions where duration/60>=5 and duration/60<10
union
select '[10-15>' as bin, count(*) as total from Sessions where duration/60>=10 and duration/60<15
union
select '15 or more'as bin, count(*) as total from Sessions where duration/60>=15

還有很多其他解法

select a.bin, count(b.bin) as total
from
(
    select '[0-5>' as bin union select '[5-10>' as bin union select '[10-15>' as bin union select '15 or more' as bin 
)a
left join 
(
    select case
        when duration < 300 then '[0-5>'
        when duration >= 300 and duration < 600 then '[5-10>'
        when duration >= 600 and duration < 900 then '[10-15>'
        else '15 or more'
        end bin
    from Sessions 
)b
on a.bin = b.bin
group by a.bin

1440. 計算布爾表達(dá)式的值

難度中等

SQL架構(gòu)

表 Variables:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
name 是該表主鍵.
該表包含了存儲的變量及其對應(yīng)的值.

表 Expressions:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) 是該表主鍵.
該表包含了需要計算的布爾表達(dá)式.
operator 是枚舉類型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保證存在于 Variables 表單中.

寫一個 SQL 查詢, 以計算表 Expressions 中的布爾表達(dá)式.

返回的結(jié)果表沒有順序要求.

查詢結(jié)果格式如下例所示.

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 表中的每一個布爾表達(dá)式的值.

select e.left_operand,e.operator,e.right_operand,
case e.operator
    when '>' then if(v1.value>v2.value,'true','false')
    when '<' then if(v1.value<v2.value,'true','false')
    else  if(v1.value=v2.value,'true','false')
end value
from Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand

1445. 蘋果和桔子

難度中等

SQL架構(gòu)

表: Sales

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    | 
| sold_num      | int     | 
+---------------+---------+
(sale_date,fruit) 是該表主鍵.
該表包含了每一天中"蘋果" 和 "桔子"的銷售情況.

寫一個 SQL 查詢, 報告每一天蘋果和桔子銷售的數(shù)目的差異.

返回的結(jié)果表, 按照格式為 ('YYYY-MM-DD') 的 sale_date 排序.

查詢結(jié)果表如下例所示:

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  sale_date,sold_num-ld diff
from 
(
select sale_date,sold_num , fruit ,lead(sold_num ,1,null) over(partition by  sale_date ) ld
from Sales
)t1
where fruit='apples'

1454. 活躍用戶

難度中等

SQL架構(gòu)

表 Accounts:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是該表主鍵.
該表包含賬戶 id 和賬戶的用戶名.

表 Logins:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
該表無主鍵, 可能包含重復(fù)項.
該表包含登錄用戶的賬戶 id 和登錄日期. 用戶也許一天內(nèi)登錄多次.

寫一個 SQL 查詢, 找到活躍用戶的 id 和 name.

活躍用戶是指那些至少連續(xù) 5 天登錄賬戶的用戶.

返回的結(jié)果表按照 id 排序.

結(jié)果表格式如下例所示:

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 天內(nèi)登錄了 2 次, 所以, Winston 不是活躍用戶.
id = 7 的用戶 Jonathon 在不同的 6 天內(nèi)登錄了 7 次, , 6 天中有 5 天是連續(xù)的, 所以, Jonathan 是活躍用戶.

后續(xù)問題:
如果活躍用戶是那些至少連續(xù) n 天登錄賬戶的用戶, 你能否寫出通用的解決方案?

select t3.id,name
from 
(
    select distinct id
    from 
    (
        select id,login_date,lead(login_date,4,null) over(partition by id order by login_date) ld
        from 
        (
            select id,login_date 
            from Logins
            group by id,login_date
        )t1
    )t2
    where datediff(ld,login_date)=4
)t3
left join Accounts a
on t3.id = a.id

注意用戶當(dāng)天重復(fù)登入

1459. 矩形面積

難度中等

SQL架構(gòu)

表: Points

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| x_value       | int     |
| y_value       | int     |
+---------------+---------+
id 是該表主鍵.
每個點都表示為二維空間 (x_value, y_value).

寫一個 SQL 語句, 報告由表中任意兩點可以形成的所有可能的矩形.

結(jié)果表中的每一行包含三列 (p1, p2, area) 如下:

p1 和 p2 是矩形兩個對角的 id 且 p1 < p2.
矩形的面積由列 area 表示.

請按照面積大小降序排列，如果面積相同的話, 則按照 p1 和 p2 升序?qū)Y(jié)果表排序

Points 表:
+----------+-------------+-------------+
| id       | x_value     | y_value     |
+----------+-------------+-------------+
| 1        | 2           | 8           |
| 2        | 4           | 7           |
| 3        | 2           | 10          |
+----------+-------------+-------------+

Result 表:
+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 6           |
| 1        | 2           | 2           |
+----------+-------------+-------------+

p1 應(yīng)該小于 p2 并且面積大于 0.
p1 = 1 且 p2 = 2 時, 面積等于 |2-4| * |8-7| = 2.
p1 = 2 且 p2 = 3 時, 面積等于 |4-2| * |7-10| = 6.
p1 = 1 且 p2 = 3 時, 是不可能為矩形的, 因為面積等于 0.

select a.id P1,b.id P2,abs(a.x_value-b.x_value)*abs(a.y_value-b.y_value) as area
from Points a,Points b
where a.id<b.id and a.x_value != b.x_value and a.y_value != b.y_value
order by area desc,P1 ,P2

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末牺弹，一起剝皮案震驚了整個濱河市浦马，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌张漂，老刑警劉巖晶默，帶你破解...
沈念sama閱讀 218,640評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異航攒，居然都是意外死亡磺陡，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,254評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來币他，“玉大人坞靶，你說我怎么就攤上這事『ぃ” “怎么了彰阴？”我有些...
開封第一講書人閱讀 165,011評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長拍冠。經(jīng)常有香客問我尿这，道長，這世上最難降的妖魔是什么庆杜？我笑而不...
開封第一講書人閱讀 58,755評論 1贊 294
?港島之戀（遺憾婚禮）
正文為了忘掉前任射众，我火速辦了婚禮，結(jié)果婚禮上晃财，老公的妹妹穿的比我還像新娘叨橱。我一直安慰自己，他們只是感情好拓劝，可當(dāng)我...
茶點故事閱讀 67,774評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布雏逾。她就那樣靜靜地躺著，像睡著了一般郑临。火紅的嫁衣襯著肌膚如雪栖博。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,610評論 1贊 305
城市分裂傳說
那天厢洞，我揣著相機與錄音仇让，去河邊找鬼。笑死躺翻，一個胖子當(dāng)著我的面吹牛丧叽，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播公你，決...
沈念sama閱讀 40,352評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼勘纯，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了肝断？” 一聲冷哼從身側(cè)響起鞠评，我...
開封第一講書人閱讀 39,257評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎剪芥，沒想到半個月后垄开，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,717評論 1贊 315
?護(hù)林員之死
正文獨居荒郊野嶺守林人離奇死亡税肪，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,894評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年溉躲，在試婚紗的時候發(fā)現(xiàn)自己被綠了榜田。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 40,021評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡锻梳，死狀恐怖箭券，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情唱蒸，我是刑警寧澤邦鲫，帶...
沈念sama閱讀 35,735評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站神汹，受9級特大地震影響庆捺，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜屁魏，卻給世界環(huán)境...
茶點故事閱讀 41,354評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一滔以、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧氓拼，春花似錦你画、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,936評論 0贊 22
一樁弒父案坏匪，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至撬统，卻和暖如春适滓，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背恋追。一陣腳步聲響...
開封第一講書人閱讀 33,054評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工凭迹，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人苦囱。一個月前我還...
沈念sama閱讀 48,224評論 3贊 371
代替公主和親
正文我出身青樓嗅绸，卻偏偏與公主長得像，于是被迫代替她去往敵國和親撕彤。傳聞我的和親對象是個殘疾皇子鱼鸠，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 44,974評論 2贊 355

sql刷題筆記（六）

推薦閱讀更多精彩內(nèi)容