今天的主题是控制流。

1393. Capital Gain/Loss

Analysis

按照买卖的不同价格，算出股票的总盈利。

Code

一开始想通过连接查询然后再做差求和得到结果，结果连接过程中消除不掉笛卡尔积，只能尝试其他思路了。

1
2
3

select stock_name, sum(if(operation = 'Sell', price, 0)) - sum(if(operation = 'Buy', price, 0)) as capital_gain_loss
from Stocks
group by stock_name

再回头看这道题的思路，果然是对应控制流的主题啊。
实际上，这个题用 CASE WHEN 也是可以解决的：

1
2
3

select stock_name, sum(case operation when 'Sell' then price else -price end) as capital_gain_loss
from Stocks
group by stock_name

1407. Top Travellers

Analysis

算出每个人的旅行总路程。

Code

按照上一题的类似的思路，可以得到下面的代码：

select name, sum(distance) as travelled_distance
from Users as u, Rides as r
where u.id = r.user_id
group by name
order by travelled_distance desc, name

但是现在有一个问题，有一个人的路程是 0，这个没查询出来。也就是说，这个题需要用到连接查询，并且在某个表中没有匹配时，返回 0。那么，不妨用左连接来做这个事情：

select name, ifnull(sum(distance), 0) as travelled_distance
from Users as u
left join Rides as r
on u.id = r.user_id
group by name
order by travelled_distance desc, name

1158. Market Analysis I

Analysis

给了 3 个表，题意是求出 2019 年内员工的订单数，并且给出员工入职日期，同时没有订单的需要返回 0。

Code

先统计出订单数：

select buyer_id, count(order_id) as cnt
from Orders as o
where o.order_date >= '2019-01-01' and o.order_date <= "2019-12-31"
group by buyer_id

然后用 LEFT JOIN 连接查询，没有匹配就返回 0：

select u.user_id as buyer_id, join_date, ifnull(t1.cnt, 0) as orders_in_2019
from Users as u
left join (
    select buyer_id, count(order_id) as cnt
    from Orders as o
    where o.order_date >= '2019-01-01' and o.order_date <= "2019-12-31"
    group by buyer_id
) as t1
on u.user_id = t1.buyer_id

Summary

连接查询用的不是很熟练...