每日刷力扣SQL题(五)

1164.指定日期的产品价格

        一、方法：使用left join 和 ifnull
        思路

        本题的关键点在找到 2019-08-16 前所有有改动的产品及其最新价格和没有没有修改过价格的产品。 我们可以先找到所有的产品，再找到所有 2019-08-16 前有修改的产品和他们最新的价格，使用 left join 将两个查询联合。如果产品没有价格，说明没有修改过，设置为 10，如果有价格，设置为最新的价格。

找出所有的产品

找到 2019-08-16 前所有有改动的产品的最新价格。

上面两步已经找到了所有的产品和已经修改过价格的产品。使用 left join 得到所有产品的最新价格，如果没有设置为 10。

select p1.product_id , ifNULL(p2.new_price,10) as price
from
(select distinct product_id from Products)
as p1
left join 
( select product_id ,new_price
from Products
where  (product_id , change_date) in  
(select product_id, max(change_date) as change_date From Products where change_date <='2019-08-16' group by product_id )
group by product_id ) as p2 on p1.product_id = p2.product_id

二、方法：使用UNION关键字 合并多个查询集的结果

-- Subquery 1: Get the latest product_id and change_date on or before '2019-08-16'
SELECT product_id, new_price as price
FROM Products
WHERE (product_id, change_date) in (SELECT product_id, max(change_date) as change_date
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY product_id)

UNION ALL

-- Subquery 2: Get product_id where the earliest change_date is after '2019-08-16' with a fixed price of 10
SELECT product_id, 10 as price
FROM Products
GROUP BY product_id
HAVING MIN(change_date)> '2019-08-16';

1204.最后一个能进巴士的人

 思路：按照turn排序的，按照weight进行累加，按照累加的大小进行倒序排序，通过limit 1 选取最后一位上车的人

# Write your MySQL query statement below
select person_name 
from
(select person_name,
sum(weight) over(order by turn) as current_weight 
from Queue) as a 
where  current_weight <=1000
order by current_weight desc
limit 1 

1907.按分类统计薪水

使用UNION关键字联合查询

-- select category,count(*) as accounts_count 
-- from
-- (select * ,
--     CASE 
--         WHEN income < 20000 then 'Low Salary'
--         WHEN income >= 20000 and income <= 50000 then 'Average Salary'
--         WHEN income > 50000 then 'High Salary'
--     END as category
-- from Accounts ) as a
-- group by category
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

其中联合查询语句 可以用CASEWHEN来实现

以下为‘Low Salary’为例子

SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts