71.即時(shí)事物配送2
寫一條 SQL 查詢語(yǔ)句獲取即時(shí)訂單在所有用戶的首次訂單中的比例。保留兩位小數(shù)。
配送表: Delivery
+-----------------------------+---------+
| Column Name | Type |
+-----------------------------+---------+
| delivery_id | int |
| customer_id | int |
| order_date | date |
| customer_pref_delivery_date | date |
+-----------------------------+---------+
delivery_id 是表的主鍵。
該表保存著顧客的食物配送信息，顧客在某個(gè)日期下了訂單，并指定了一個(gè)期望的配送日期（和下單日期相同或者在那之后）。
如果顧客期望的配送日期和下單日期相同，則該訂單稱為 「即時(shí)訂單」，否則稱為「計(jì)劃訂單」。
「首次訂單」是顧客最早創(chuàng)建的訂單。我們保證一個(gè)顧客只會(huì)有一個(gè)「首次訂單」。
寫一條 SQL 查詢語(yǔ)句獲取即時(shí)訂單在所有用戶的首次訂單中的比例。保留兩位小數(shù)。
查詢結(jié)果如下所示：
Delivery 表：
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-12 |
| 4 | 3 | 2019-08-24 | 2019-08-24 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
+-------------+-------------+------------+-----------------------------+
Result 表：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00 |
+----------------------+
1 號(hào)顧客的 1 號(hào)訂單是首次訂單，并且是計(jì)劃訂單。
2 號(hào)顧客的 2 號(hào)訂單是首次訂單，并且是即時(shí)訂單。
3 號(hào)顧客的 5 號(hào)訂單是首次訂單，并且是計(jì)劃訂單。
4 號(hào)顧客的 7 號(hào)訂單是首次訂單，并且是即時(shí)訂單。
因此，一半顧客的首次訂單是即時(shí)的
--我的寫法,考慮可能有的用戶沒有首次購(gòu)買時(shí)間?,可以優(yōu)化一下,把獲取總的首次購(gòu)買的總數(shù)直接count(1) 而不是用子查詢獲取
select
round((count(1) / (select count(distinct customer_id) from Delivery)) *100,2) as immediate_percentage
from
Delivery
where order_date = customer_pref_delivery_date
and (customer_id,order_date) in
(
select
customer_id,min(order_date) as da
from
Delivery
group by customer_id
)
select round(sum(order_date=customer_pref_delivery_date)/count(*)*100,2) immediate_percentage
from(
select order_date,customer_pref_delivery_date,
rank() over (partition by customer_id order by order_date) r
from Delivery
) a
where r=1
72.重新格式化部門表
編寫一個(gè) SQL 查詢來重新格式化表，使得新的表中有一個(gè)部門 id 列和一些對(duì)應(yīng) 每個(gè)月 的收入（revenue）列。
部門表 Department：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的聯(lián)合主鍵。
這個(gè)表格有關(guān)于每個(gè)部門每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
編寫一個(gè) SQL 查詢來重新格式化表，使得新的表中有一個(gè)部門 id 列和一些對(duì)應(yīng) 每個(gè)月 的收入（revenue）列。
查詢結(jié)果格式如下面的示例所示：
Department 表：
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查詢得到的結(jié)果表：
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
注意，結(jié)果表有 13 列 (1個(gè)部門 id 列 + 12個(gè)月份的收入列)。
-- 簡(jiǎn)單題,考察group by 和聚合函數(shù)
select
id,
sum(if(month = "Jan",revenue,null)) as Jan_Revenue,
sum(if(month = "Feb",revenue,null)) as Feb_Revenue,
sum(if(month = "Mar",revenue,null)) as Mar_Revenue,
sum(if(month = "Apr",revenue,null)) as Apr_Revenue,
sum(if(month = "May",revenue,null)) as May_Revenue,
sum(if(month = "Jun",revenue,null)) as Jun_Revenue,
sum(if(month = "Jul",revenue,null)) as Jul_Revenue,
sum(if(month = "Aug",revenue,null)) as Aug_Revenue,
sum(if(month = "Sep",revenue,null)) as Sep_Revenue,
sum(if(month = "Oct",revenue,null)) as Oct_Revenue,
sum(if(month = "Nov",revenue,null)) as Nov_Revenue,
sum(if(month = "Dec",revenue,null)) as Dec_Revenue
from
Department
group by id
select
id
, sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue
, sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue
, sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue
, sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue
, sum(case `month` when 'May' then revenue else null end) as May_Revenue
, sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue
, sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue
, sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue
, sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue
, sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue
, sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue
, sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id
73.每月交易1
編寫一個(gè) sql 查詢來查找每個(gè)月和每個(gè)國(guó)家/地區(qū)的事務(wù)數(shù)及其總金額、已批準(zhǔn)的事務(wù)數(shù)及其總金額。
Table: Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| country | varchar |
| state | enum |
| amount | int |
| trans_date | date |
+---------------+---------+
id 是這個(gè)表的主鍵。
該表包含有關(guān)傳入事務(wù)的信息。
state 列類型為 “[”批準(zhǔn)“，”拒絕“] 之一。
編寫一個(gè) sql 查詢來查找每個(gè)月和每個(gè)國(guó)家/地區(qū)的事務(wù)數(shù)及其總金額、已批準(zhǔn)的事務(wù)數(shù)及其總金額。
查詢結(jié)果格式如下所示：
Transactions table:
+------+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+------+---------+----------+--------+------------+
| 121 | US | approved | 1000 | 2018-12-18 |
| 122 | US | declined | 2000 | 2018-12-19 |
| 123 | US | approved | 2000 | 2019-01-01 |
| 124 | DE | approved | 2000 | 2019-01-07 |
+------+---------+----------+--------+------------+
Result table:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12 | US | 2 | 1 | 3000 | 1000 |
| 2019-01 | US | 1 | 1 | 2000 | 2000 |
| 2019-01 | DE | 1 | 1 | 2000 | 2000 |
+----------+---------+-------------+----------------+--------------------+-----------------------+
-- 簡(jiǎn)單題,可以使用date_format或者left函數(shù)
select
substr(trans_date,1,7) month , country,count(state) trans_count,
sum(state = "approved") approved_count ,sum(amount) trans_total_amount,
sum(if(state = "approved",amount,0)) approved_total_amount
from
Transactions
group by country,substr(trans_date,1,7)
select date_format(trans_date,"%Y-%m") month
,country,count(id) trans_count
,count(if(state<>"approved",null,state)) approved_count
,sum(amount) trans_total_amount
,sum(amount*(state<>"declined")) approved_total_amount
from transactions
group by date_format(trans_date,"%Y-%m"),country;
注意在group by之后使用count(字段=?) 統(tǒng)計(jì)的是全部的行,如果要過濾要使用case when 或者 if 來過濾例如:
count(if(state<>"approved",null,state)) approved_count
74.錦標(biāo)賽優(yōu)勝者
編寫一個(gè) SQL 查詢來查找每組中的獲勝者。
Players 玩家表
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| player_id | int |
| group_id | int |
+-------------+-------+
player_id 是此表的主鍵。
此表的每一行表示每個(gè)玩家的組。
Matches 賽事表
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| match_id | int |
| first_player | int |
| second_player | int |
| first_score | int |
| second_score | int |
+---------------+---------+
match_id 是此表的主鍵。
每一行是一場(chǎng)比賽的記錄，first_player 和 second_player 表示該場(chǎng)比賽的球員 ID。
first_score 和 second_score 分別表示 first_player 和 second_player 的得分。
你可以假設(shè)，在每一場(chǎng)比賽中，球員都屬于同一組。
每組的獲勝者是在組內(nèi)累積得分最高的選手。如果平局，player_id 最小 的選手獲勝。
編寫一個(gè) SQL 查詢來查找每組中的獲勝者。
查詢結(jié)果格式如下所示
Players 表:
+-----------+------------+
| player_id | group_id |
+-----------+------------+
| 15 | 1 |
| 25 | 1 |
| 30 | 1 |
| 45 | 1 |
| 10 | 2 |
| 35 | 2 |
| 50 | 2 |
| 20 | 3 |
| 40 | 3 |
+-----------+------------+
Matches 表:
+------------+--------------+---------------+-------------+--------------+
| match_id | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1 | 15 | 45 | 3 | 0 |
| 2 | 30 | 25 | 1 | 2 |
| 3 | 30 | 15 | 2 | 0 |
| 4 | 40 | 20 | 5 | 2 |
| 5 | 35 | 50 | 1 | 1 |
+------------+--------------+---------------+-------------+--------------+
Result 表:
+-----------+------------+
| group_id | player_id |
+-----------+------------+
| 1 | 15 |
| 2 | 35 |
| 3 | 40 |
+-----------+------------+
--我的寫法先獲取每個(gè)人的分?jǐn)?shù),然后在組內(nèi)比較獲取最大值
select
group_id,player_id
from
(
select
group_id,player_id,row_number() over( partition by group_id order by score desc ,player_id asc ) as rn
from
Players
join
(
select
player,sum(score) as score
from
(
select
first_player as player ,sum(first_score) as score
from
Matches
group by first_player
union all
select
second_player ,sum(second_score)
from
Matches
group by second_player
)
group by player
) t2
on Players.player_id = t2.player
)
where rn =1
-- 使用first_value可以減少嵌套一層select
SELECT DISTINCT group_id,
FIRST_VALUE(player_id) OVER(PARTITION BY group_id ORDER BY score DESC, player_id) AS player_id
FROM
(SELECT p.group_id,
p.player_id,
SUM(score)AS score
FROM
(SELECT first_player AS player_id, first_score AS score
FROM Matches
UNION ALL
SELECT second_player AS player_id, second_score AS score
FROM Matches) t1
LEFT JOIN
Players p
ON t1.player_id = p.player_id
GROUP BY player_id) t2
-- 偷懶寫法,group by 默認(rèn)返回第一行
select group_id, player_id
from (
select players.*, sum(if(player_id = first_player, first_score, second_score)) score
from players join matches
on player_id = first_player or player_id = second_player
group by player_id
order by score desc, player_id
) tmp
group by group_id
75.最后一個(gè)能進(jìn)入電梯的人(自連接)
寫一條 SQL 查詢語(yǔ)句查找最后一個(gè)能進(jìn)入電梯且不超過重量限制的 person_name 。題目確保隊(duì)列中第一位的人可以進(jìn)入電梯 。
表: Queue
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| person_id | int |
| person_name | varchar |
| weight | int |
| turn | int |
+-------------+---------+
person_id 是這個(gè)表的主鍵。
該表展示了所有等待電梯的人的信息。
表中 person_id 和 turn 列將包含從 1 到 n 的所有數(shù)字，其中 n 是表中的行數(shù)。
電梯最大載重量為 1000。
寫一條 SQL 查詢語(yǔ)句查找最后一個(gè)能進(jìn)入電梯且不超過重量限制的 person_name 。題目確保隊(duì)列中第一位的人可以進(jìn)入電梯 。
查詢結(jié)果如下所示 :
Queue 表
+-----------+-------------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------------+--------+------+
| 5 | George Washington | 250 | 1 |
| 3 | John Adams | 350 | 2 |
| 6 | Thomas Jefferson | 400 | 3 |
| 2 | Will Johnliams | 200 | 4 |
| 4 | Thomas Jefferson | 175 | 5 |
| 1 | James Elephant | 500 | 6 |
+-----------+-------------------+--------+------+
Result 表
+-------------------+
| person_name |
+-------------------+
| Thomas Jefferson |
+-------------------+
為了簡(jiǎn)化，Queue 表按 turn 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 將可以進(jìn)入電梯,因?yàn)樗麄兊捏w重和為 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一個(gè)體重合適并進(jìn)入電梯的人。
--我的寫法,窗口寫法
select
person_name
from
(
select
person_name,rank() over(order by weight_rn desc ) as rn
from
(
select
person_name,
sum(weight) over(order by turn ) as weight_rn
from
Queue
)
where weight_rn <= 1000
)
where rn =1
--自連接,where t1.turn >= t2.turn計(jì)算直到當(dāng)前的總的質(zhì)量
select t1.person_name
from queue t1, queue t2
where t1.turn >= t2.turn
group by t1.turn
having sum(t2.weight) <= 1000
order by t1.turn desc
limit 1
76.每月交易2(tag)☆
編寫一個(gè) SQL 查詢，以查找每個(gè)月和每個(gè)國(guó)家/地區(qū)的已批準(zhǔn)交易的數(shù)量及其總金額、退單的數(shù)量及其總金額。
注意：在您的查詢中，給定月份和國(guó)家，忽略所有為零的行。
Transactions 記錄表
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| id | int |
| country | varchar |
| state | enum |
| amount | int |
| trans_date | date |
+----------------+---------+
id 是這個(gè)表的主鍵。
該表包含有關(guān)傳入事務(wù)的信息。
狀態(tài)列是類型為 [approved（已批準(zhǔn)）、declined（已拒絕）] 的枚舉。
Chargebacks 表
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| trans_id | int |
| charge_date | date |
+----------------+---------+
退單包含有關(guān)放置在事務(wù)表中的某些事務(wù)的傳入退單的基本信息。
trans_id 是 transactions 表的 id 列的外鍵。
每項(xiàng)退單都對(duì)應(yīng)于之前進(jìn)行的交易，即使未經(jīng)批準(zhǔn)。
編寫一個(gè) SQL 查詢，以查找每個(gè)月和每個(gè)國(guó)家/地區(qū)的已批準(zhǔn)交易的數(shù)量及其總金額、退單的數(shù)量及其總金額。
注意：在您的查詢中，給定月份和國(guó)家，忽略所有為零的行。
查詢結(jié)果格式如下所示：
Transactions 表：
+------+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+------+---------+----------+--------+------------+
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | US | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | declined | 4000 | 2019-06-13 |
| 105 | US | approved | 5000 | 2019-06-15 |
+------+---------+----------+--------+------------+
Chargebacks 表：
+------------+------------+
| trans_id | trans_date |
+------------+------------+
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
+------------+------------+
Result 表：
+----------+---------+----------------+-----------------+-------------------+--------------------+
| month | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+----------+---------+----------------+-----------------+-------------------+--------------------+
| 2019-05 | US | 1 | 1000 | 1 | 2000 |
| 2019-06 | US | 2 | 8000 | 1 | 1000 |
| 2019-09 | US | 0 | 0 | 1 | 5000 |
+----------+---------+----------------+-----------------+-------------------+------------------
-- 總少了一個(gè)國(guó)家,我裂開了.....沒有full join難受
select
t1.dt as month ,t1.country,approved_count,approved_amount,chargeback_count,chargeback_amount
from
(
select
country,left(trans_date,7) as dt ,ifnull(sum(state ="approved"),0) as approved_count,
ifnull(sum(if(state ="approved",amount,0)),0) as approved_amount
from
Transactions
group by country, left(trans_date,7)
) t1
left join
(
select
country,left(Chargebacks.trans_date,7) as dt ,ifnull(count(1),0) as chargeback_count ,ifnull(sum(amount),0) as chargeback_amount
from
Transactions
join Chargebacks on Transactions.id = Chargebacks.trans_id and left(Chargebacks.trans_date,7) = left( Transactions.trans_date,7)
group by country,left(Chargebacks.trans_date,7)
) t2
on t1.country = t2.country and t1.dt = t2.dt
--學(xué)習(xí)使用tag用法,妙!!!!
select
date_format(trans_date, '%Y-%m') as month,
country,
sum(if(state = 'approved',1,0)) as approved_count,
sum(if(state = 'approved',amount,0)) as approved_amount,
sum(if(state = 'chargeback',1,0)) as chargeback_count,
sum(if(state = 'chargeback',amount,0)) as chargeback_amount
from
(select
*
from
transactions
union all
select
trans_id as id,
country,
'chargeback' as state,
amount,
c.trans_date
from
chargebacks c left join transactions t
on t.id = c.trans_id) as temp
group by 1,2
having approved_count <> 0 or chargeback_count <> 0
防止為0
having approved_count <> 0 or chargeback_count <> 0
77.查詢結(jié)果的質(zhì)量與占比
編寫一組 SQL 來查找每次查詢的名稱(query_name)、質(zhì)量(quality) 和 劣質(zhì)查詢百分比(poor_query_percentage)。
質(zhì)量(quality) 和劣質(zhì)查詢百分比(poor_query_percentage) 都應(yīng)四舍五入到小數(shù)點(diǎn)后兩位。
查詢表 Queries：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
此表沒有主鍵，并可能有重復(fù)的行。
此表包含了一些從數(shù)據(jù)庫(kù)中收集的查詢信息。
“位置”（position）列的值為 1 到 500 。
“評(píng)分”（rating）列的值為 1 到 5 。評(píng)分小于 3 的查詢被定義為質(zhì)量很差的查詢。
將查詢結(jié)果的質(zhì)量 quality 定義為：
各查詢結(jié)果的評(píng)分與其位置之間比率的平均值。
將劣質(zhì)查詢百分比 poor_query_percentage 為：
評(píng)分小于 3 的查詢結(jié)果占全部查詢結(jié)果的百分比。
編寫一組 SQL 來查找每次查詢的名稱(query_name)、質(zhì)量(quality) 和 劣質(zhì)查詢百分比(poor_query_percentage)。
質(zhì)量(quality) 和劣質(zhì)查詢百分比(poor_query_percentage) 都應(yīng)四舍五入到小數(shù)點(diǎn)后兩位。
查詢結(jié)果格式如下所示：
Queries table:
+------------+-------------------+----------+--------+
| query_name | result | position | rating |
+------------+-------------------+----------+--------+
| Dog | Golden Retriever | 1 | 5 |
| Dog | German Shepherd | 2 | 5 |
| Dog | Mule | 200 | 1 |
| Cat | Shirazi | 5 | 2 |
| Cat | Siamese | 3 | 3 |
| Cat | Sphynx | 7 | 4 |
+------------+-------------------+----------+--------+
Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+
Dog 查詢結(jié)果的質(zhì)量為 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查詢結(jié)果的劣質(zhì)查詢百分比為 (1 / 3) * 100 = 33.33
Cat 查詢結(jié)果的質(zhì)量為 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查詢結(jié)果的劣質(zhì)查詢百分比為 (1 / 3) * 100 = 33.33
--讀懂題意,簡(jiǎn)單題
select
query_name,round(sum(rating/position) /count(1),2) as quality,
round(sum(rating < 3) /count(1) *100 ,2)as poor_query_percentage
from
Queries
group by query_name
SELECT
query_name,
ROUND(AVG(rating/position), 2) quality,
ROUND(avg(rating < 3) * 100,2) poor_query_percentage
FROM Queries
GROUP BY query_name
78.查詢球隊(duì)積分
寫出一條SQL語(yǔ)句以查詢每個(gè)隊(duì)的 team_id，team_name 和 num_points。結(jié)果根據(jù) num_points 降序排序，如果有兩隊(duì)積分相同，那么這兩隊(duì)按 team_id 升序排序
Table: Teams
+---------------+----------+
| Column Name | Type |
+---------------+----------+
| team_id | int |
| team_name | varchar |
+---------------+----------+
此表的主鍵是 team_id，表中的每一行都代表一支獨(dú)立足球隊(duì)。
Table: Matches
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| match_id | int |
| host_team | int |
| guest_team | int |
| host_goals | int |
| guest_goals | int |
+---------------+---------+
此表的主鍵是 match_id，表中的每一行都代表一場(chǎng)已結(jié)束的比賽，比賽的主客隊(duì)分別由它們自己的 id 表示，他們的進(jìn)球由 host_goals 和 guest_goals 分別表示。
積分規(guī)則如下：
贏一場(chǎng)得三分；
平一場(chǎng)得一分；
輸一場(chǎng)不得分。
寫出一條SQL語(yǔ)句以查詢每個(gè)隊(duì)的 team_id，team_name 和 num_points。結(jié)果根據(jù) num_points 降序排序，如果有兩隊(duì)積分相同，那么這兩隊(duì)按 team_id 升序排序。
查詢結(jié)果格式如下：
Teams table:
+-----------+--------------+
| team_id | team_name |
+-----------+--------------+
| 10 | Leetcode FC |
| 20 | NewYork FC |
| 30 | Atlanta FC |
| 40 | Chicago FC |
| 50 | Toronto FC |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id | host_team | guest_team | host_goals | guest_goals |
+------------+--------------+---------------+-------------+--------------+
| 1 | 10 | 20 | 3 | 0 |
| 2 | 30 | 10 | 2 | 2 |
| 3 | 10 | 50 | 5 | 1 |
| 4 | 20 | 30 | 1 | 0 |
| 5 | 50 | 30 | 1 | 0 |
+------------+--------------+---------------+-------------+--------------+
Result table:
+------------+--------------+---------------+
| team_id | team_name | num_points |
+------------+--------------+---------------+
| 10 | Leetcode FC | 7 |
| 20 | NewYork FC | 3 |
| 50 | Toronto FC | 3 |
| 30 | Atlanta FC | 1 |
| 40 | Chicago FC | 0 |
+------------+--------------+---------------+
--我的寫法,讀懂題意
select
Teams.team_id team_id,team_name, ifnull(num_points,0) as num_points
from
Teams left join
(
select
team_id,sum(points) as num_points
from
(
select
host_team as team_id,sum(case when host_goals > guest_goals then 3 when host_goals < guest_goals then 0 else 1 end ) as points
from
Matches
group by host_team
union all
select
guest_team ,sum(case when host_goals > guest_goals then 0 when host_goals < guest_goals then 3 else 1 end )
from
Matches
group by guest_team
) t1
group by team_id
) t2
on Teams.team_id = t2.team_id
order by num_points desc ,team_id
--評(píng)論區(qū)寫法
select t.team_id,t.team_name,
ifnull(sum(case when m.host_goals>m.guest_goals and t.team_id=m.host_team then 3
when m.host_goals=m.guest_goals and t.team_id=m.host_team then 1
when m.host_goals<m.guest_goals and t.team_id=m.host_team then 0
when m.host_goals>m.guest_goals and t.team_id=m.guest_team then 0
when m.host_goals=m.guest_goals and t.team_id=m.guest_team then 1
when m.host_goals<m.guest_goals and t.team_id=m.guest_team then 3
end),0) as num_points
from Teams t,Matches m
group by t.team_id
order by num_points desc,t.team_id asc
79.報(bào)告系統(tǒng)狀態(tài)的連續(xù)日期
編寫一個(gè) SQL 查詢 2019-01-01 到 2019-12-31 期間任務(wù)連續(xù)同狀態(tài) period_state 的起止日期（start_date 和 end_date）。即如果任務(wù)失敗了，就是失敗狀態(tài)的起止日期，如果任務(wù)成功了，就是成功狀態(tài)的起止日期。
Table: Failed
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
該表主鍵為 fail_date。
該表包含失敗任務(wù)的天數(shù).
Table: Succeeded
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
該表主鍵為 success_date。
該表包含成功任務(wù)的天數(shù).
系統(tǒng) 每天 運(yùn)行一個(gè)任務(wù)。每個(gè)任務(wù)都獨(dú)立于先前的任務(wù)。任務(wù)的狀態(tài)可以是失敗或是成功。
編寫一個(gè) SQL 查詢 2019-01-01 到 2019-12-31 期間任務(wù)連續(xù)同狀態(tài) period_state 的起止日期（start_date 和 end_date）。即如果任務(wù)失敗了，就是失敗狀態(tài)的起止日期，如果任務(wù)成功了，就是成功狀態(tài)的起止日期。
最后結(jié)果按照起始日期 start_date 排序
查詢結(jié)果樣例如下所示:
Failed table:
+-------------------+
| fail_date |
+-------------------+
| 2018-12-28 |
| 2018-12-29 |
| 2019-01-04 |
| 2019-01-05 |
+-------------------+
Succeeded table:
+-------------------+
| success_date |
+-------------------+
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-06 |
+-------------------+
Result table:
+--------------+--------------+--------------+
| period_state | start_date | end_date |
+--------------+--------------+--------------+
| succeeded | 2019-01-01 | 2019-01-03 |
| failed | 2019-01-04 | 2019-01-05 |
| succeeded | 2019-01-06 | 2019-01-06 |
+--------------+--------------+--------------+
結(jié)果忽略了 2018 年的記錄，因?yàn)槲覀冎魂P(guān)心從 2019-01-01 到 2019-12-31 的記錄
從 2019-01-01 到 2019-01-03 所有任務(wù)成功，系統(tǒng)狀態(tài)為 "succeeded"。
從 2019-01-04 到 2019-01-05 所有任務(wù)失敗，系統(tǒng)狀態(tài)為 "failed"。
從 2019-01-06 到 2019-01-06 所有任務(wù)成功，系統(tǒng)狀態(tài)為 "succeeded"。
--終于跑對(duì)了,Oracle真麻煩
select
'succeeded' as period_state ,to_char(min(success_date),'yyyy-mm-dd') as start_date,
to_char(max(success_date),'yyyy-mm-dd') as end_date
from
(
select
success_date,
(success_date - row_number() over(order by success_date) ) rn
from
Succeeded
where to_char(success_date,'yyyy-mm-dd') >= '2019-01-01' and to_char(success_date,'yyyy-mm-dd') <= '2019-12-31'
)
group by rn
union all
select
'failed',to_char(min(fail_date),'yyyy-mm-dd'),to_char(max(fail_date),'yyyy-mm-dd')
from
(
select
fail_date,
( fail_date - row_number() over(order by fail_date ) ) rn
from
Failed
where to_char(fail_date,'yyyy-mm-dd') between '2019-01-01' and '2019-12-31'
)
group by rn
order by start_date
-- 整體求,注意不要排序簡(jiǎn)單的連續(xù)登陸案例變形題
select state as period_state,min(dt) as start_date,max(dt) as end_date
from
(
select *,subdate(dt,rank() over(partition by state order by dt)) as dif
from
(
select 'failed' as state,fail_date as dt from failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' as state,success_date as dt from succeeded where success_date between '2019-01-01' and '2019-12-31'
)t1
)t2
group by state,dif
order by dt
80.每個(gè)帖子的評(píng)論數(shù)
編寫 SQL 語(yǔ)句以查找每個(gè)帖子的評(píng)論數(shù)。
結(jié)果表應(yīng)包含帖子的 post_id 和對(duì)應(yīng)的評(píng)論數(shù) number_of_comments 并且按 post_id 升序排列。
Submissions 可能包含重復(fù)的評(píng)論。您應(yīng)該計(jì)算每個(gè)帖子的唯一評(píng)論數(shù)。
Submissions 可能包含重復(fù)的帖子。您應(yīng)該將它們視為一個(gè)帖子。
表 Submissions 結(jié)構(gòu)如下：
+---------------+----------+
| 列名 | 類型 |
+---------------+----------+
| sub_id | int |
| parent_id | int |
+---------------+----------+
上表沒有主鍵, 所以可能會(huì)出現(xiàn)重復(fù)的行。
每行可以是一個(gè)帖子或?qū)υ撎拥脑u(píng)論。
如果是帖子的話，parent_id 就是 null。
對(duì)于評(píng)論來說，parent_id 就是表中對(duì)應(yīng)帖子的 sub_id。
編寫 SQL 語(yǔ)句以查找每個(gè)帖子的評(píng)論數(shù)。
結(jié)果表應(yīng)包含帖子的 post_id 和對(duì)應(yīng)的評(píng)論數(shù) number_of_comments 并且按 post_id 升序排列。
Submissions 可能包含重復(fù)的評(píng)論。您應(yīng)該計(jì)算每個(gè)帖子的唯一評(píng)論數(shù)。
Submissions 可能包含重復(fù)的帖子。您應(yīng)該將它們視為一個(gè)帖子。
查詢結(jié)果格式如下例所示：
Submissions table:
+---------+------------+
| sub_id | parent_id |
+---------+------------+
| 1 | Null |
| 2 | Null |
| 1 | Null |
| 12 | Null |
| 3 | 1 |
| 5 | 2 |
| 3 | 1 |
| 4 | 1 |
| 9 | 1 |
| 10 | 2 |
| 6 | 7 |
+---------+------------+
結(jié)果表：
+---------+--------------------+
| post_id | number_of_comments |
+---------+--------------------+
| 1 | 3 |
| 2 | 2 |
| 12 | 0 |
+---------+--------------------+
表中 ID 為 1 的帖子有 ID 為 3、4 和 9 的三個(gè)評(píng)論。表中 ID 為 3 的評(píng)論重復(fù)出現(xiàn)了，所以我們只對(duì)它進(jìn)行了一次計(jì)數(shù)。
表中 ID 為 2 的帖子有 ID 為 5 和 10 的兩個(gè)評(píng)論。
ID 為 12 的帖子在表中沒有評(píng)論。
表中 ID 為 6 的評(píng)論是對(duì) ID 為 7 的已刪除帖子的評(píng)論，因此我們將其忽略。
-- 簡(jiǎn)單題
select
a.sub_id as post_id ,ifnull(count(distinct b.sub_id ),0) as number_of_comments
from
(
select
distinct sub_id as sub_id
from
Submissions
where parent_id is null
) a
left join
Submissions b
on a.sub_id = b.parent_id
group by a.sub_id
select s1.sub_id post_id, count(distinct s2.sub_id) number_of_comments
from
submissions s1 left join submissions s2 on s1.sub_id = s2.parent_id
where s1.parent_id is null
group by s1.sub_id
order by 1
一個(gè)基礎(chǔ)題left join on 和 left join where 的區(qū)別,left join on 無(wú)論是否滿足都會(huì)返回左邊的數(shù)據(jù)
