1164. 指定日期的產品價格

題目鏈接：https://leetcode.cn/problems/product-price-at-a-given-date/description/?envType=study-plan-v2&envId=sql-free-50
狀態：已完成

考點：

• group by + select語句中使用聚合函數max()：獲取每個產品的最新日期
• 子查詢：獲取2019-08-16之前每個產品的最新價格
• 連接：針對未在2019-08-16之前更新價格的產品，通過連接操作產生的空值，結合ifnull()函數轉化為默認值

select T1.product_id, ifnull(new_price, 10) as price
from (select distinct product_idfrom Products
)as T1 left join (select product_id, new_pricefrom Productswhere (product_id, change_date) in (select product_id, max(change_date) as max_datefrom Productswhere datediff(change_date, "2019-08-16") <= 0group by product_id)
) as T2
on T1.product_id = T2.product_id

1204. 最后一個能進入巴士的人

題目鏈接：https://leetcode.cn/problems/last-person-to-fit-in-the-bus/description/?envType=study-plan-v2&envId=sql-free-50
狀態：需二刷，完全沒有解題思路

解題思路：首先通過連接操作獲取{1，2，3，…，i}的子集，然后計算每個子集的和，篩選出所有和<1000的子集，選取其中最大的i，即為最后一個進入巴士的人
考點：

• join + on，連接操作
• group by + having，分組操作
• order by + desc + limit k，降序排序+輸出Top-k操作

select Q1.person_name
from Queue as Q1 join Queue as Q2
on Q1.turn >= Q2.turn
group by Q1.turn
having sum(Q2.weight) <= 1000
order by sum(Q2.weight) desc
limit 1

1907. 按分類統計薪水

題目鏈接：https://leetcode.cn/problems/count-salary-categories/?envType=study-plan-v2&envId=sql-free-50
狀態：已完成

考點：使用UNION橫向連接表格
注：不需要手動創建臨時表，直接使用UNION連接多個表格即可，MYSQL會自動創建臨時表

select "Low Salary" as category, count(*) as accounts_count
from Accounts
where income < 20000
UNION
select "Average Salary" as category, count(*) as accounts_count
from Accounts
where income >= 20000 and income <= 50000
UNION
select "High Salary" as category, count(*) as accounts_count
from Accounts
where income > 50000