LeetCode---SQL刷题5

1.1890. 2020年最后一次登录

SQL语句如下:

SELECT user_id,
max(DISTINCT time_stamp) AS last_stamp
FROM Logins 
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id 

2.1741. 查找每个员工花费的总时间

SQL语句如下:

event_day  AS day,emp_id,
SUM(out_time) - SUM(in_time) AS total_time
FROM Employees 
GROUP BY emp_id,event_day  

3.1393. 股票的资本损益

SQL语句如下:

• 解题1

SELECT stock_name,SUM(
    IF(operation = 'Buy',-price,price)
)  AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

• 解题2

SELECT stock_name,SUM(
    CASE operation 
        WHEN 'Buy' THEN -price
        ELSE price
    END
)  AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

• 解题3

SELECT stock_name,SUM(
    CASE WHEN operation = 'Buy' THEN -price
    ELSE price
    END
)  AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

4.1407. 排名靠前的旅行者

SQL语句如下:

SELECT name,IFNULL(SUM(distance),0) AS  travelled_distance
FROM Users u 
LEFT JOIN Rides r ON u.id = r.user_id
GROUP BY user_id
ORDER BY travelled_distance DESC ,name  ASC

5.1158. 市场分析 I

SQL语句如下:

• 解题1

SELECT user_id AS buyer_id,join_date, SUM(IF(YEAR(order_date) = '2019',1,0)) AS orders_in_2019
FROM Users 
LEFT JOIN   Orders  ON   user_id = buyer_id
GROUP BY user_id 

• 解题2

SELECT user_id AS buyer_id,join_date, COUNT(order_id) AS orders_in_2019
FROM Users 
LEFT JOIN   Orders  ON   user_id = buyer_id AND YEAR(order_date) = 2019 
GROUP BY user_id   

备注:这道题第一个误区就是给了三张表以为都需要使用,但是根据题中提到的 "买家的订单总数" 可知就是Users和Orders这二张表进行连接,第二个误区就是查询的时候查询字段为buyer_id,但是由于分组后buyer_id可能出现null情况,并且由于user_id = buyer_id作为连接查询条件,所以这里查询的user_id 就是 buyer_id,第三个误区就是时间限制2019这里的时间针对的是订单数量的时候(对列有效),所以不能够在where中添加时间限制,所以要么在列字段直接判断要么在连接查询的时候进行判断。

6.182. 查找重复的电子邮箱

SQL语句如下:

SELECT Email
FROM Person
GROUP BY Email  HAVING COUNT(Email) > 1

备注:COUNT函数必须配合GROUP BY一起使用,不能在WHERE后面跟COUNT函数,类似的函数还有count、sum、avg、max、min。

7.1050. 合作过至少三次的演员和导演

SQL语句如下:

SELECT actor_id,director_id
FROM ActorDirector
GROUP BY actor_id,director_id HAVING COUNT(*) >= 3

8.1587. 银行账户概要 II

SQL语句如下:

SELECT name,
SUM(amount) AS balance
FROM Users u
LEFT JOIN Transactions t ON u.account = t.account 
GROUP BY t.account HAVING  balance > 10000

9.1084. 销售分析III

SQL语句如下:

SELECT p.product_id,product_name
FROM Product p
LEFT JOIN Sales s ON p.product_id  = s.product_id 
GROUP BY s.product_id HAVING  MIN(sale_date) >= '2019-01-01'  AND  MAX(sale_date) <= '2019-03-31'