【Leecode-专项突破-SQL入门】day9

今天加班~回来的有点晚了…
一共 3 道题；1 简单 2 中等

1393.股票的资本损益

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  |
+---------------+-----------+---------------+--------+
#(stock_name, day) 是这张表的主键
#operation 列使用的是一种枚举类型，包括：('Sell','Buy')
#此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
#保证股票的每次'Sell'操作前，都有相应的'Buy'操作

编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。
我采用子查询方法，根据 operation 对 price 进行标记，然后分组求和。

select t.stock_name, sum(t.price) as 'capital_gain_loss' from (
select stock_name
  ,case when operation = 'Buy' then price * (-1) else price end as 'price'
  from Stocks
) t group by t.stock_name;

推荐方法是将 case…when 套进了 sum() 函数里，节省了一步。

select stock_name, sum(
   case when operation = 'buy' then -price else price end
   ) as capital_gain_loss 
   from stocks 
   group by stock_name;

1407.排名靠前的旅行者

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

写一段 SQL , 报告每个用户的旅行距离。返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
两表进行关联，对可能存在的空值设置为0，分组求和并排序：

select u.name,
  sum(case when r.distance is null then 0 else r.distance end) as 'travelled_distance'
  from Users u left join Rides r on u.id = r.user_id
  group by u.id
  order by travelled_distance desc,name asc;

1158.市场分析 I

输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。以任意顺序返回结果表。
题目给出了许多信息，但是在本题中大部分字段都不会用到。
我在这道题中将日期也设置为关联条件，这样，在 ID 相同时，年份不同也无法关联到，值为 null，对这些 null 设置购买次数为 0，其余为 1。然后根据分组求和，即可得到结果。

select t.user_id as 'buyer_id', t.join_date, sum(t.order) as 'orders_in_2019' from(
select u.user_id, u.join_date
  ,case when o.buyer_id is not null then 1 else 0 end as 'order'
  from Users u left join Orders o 
  on u.user_id = o.buyer_id and year(o.order_date) = '2019'
) t group by t.user_id ;

官方代码是对筛选后（只包含2019年的订单）的子查询进行关联，对可能的空值赋予 0。

select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt 
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id ;

–已经凌晨了…
–大家伙儿，告辞，熬不动了