9.控制流

1.1393.股票的资本损益

i.

# Write your MySQL query statement below
SELECT
   stock_name,SUM(CASE WHEN operation = 'Buy' THEN -price ELSE price END) capital_gain_loss
FROM
   Stocks
GROUP BY stock_name;

ii.

# Write your MySQL query statement below
SELECT
   stock_name,SUM(IF(operation = 'Buy',-price,price)) capital_gain_loss
FROM
   Stocks
GROUP BY stock_name;

2.1407.排名靠前的旅行者

i.

# Write your MySQL query statement below
SELECT
   Users.name,IFNULL(SUM(Rides.distance),0) travelled_distance
FROM
   Users LEFT JOIN Rides ON Users.id = Rides.user_id
GROUP BY Users.id
ORDER BY travelled_distance DESC,name 

ii.

# Write your MySQL query statement below
SELECT
   Users.name,IF(Rides.distance,SUM(Rides.distance),0) travelled_distance
FROM
   Users LEFT JOIN Rides ON Users.id = Rides.user_id
GROUP BY Users.id
ORDER BY travelled_distance DESC,name 

iii.

coalesce函数

coalesce(表达式1,表达式2,....)

按照顺序先执行表达式1，如果不为null则返回表达式1的结果,如果为null，则往下执行表达式2，依此类推

可以用于当返回值为null时的默认返回

# Write your MySQL query statement below
SELECT
   Users.name,COALESCE(SUM(Rides.distance),0) travelled_distance
FROM
   Users LEFT JOIN Rides ON Users.id = Rides.user_id
GROUP BY Users.id
ORDER BY travelled_distance DESC,name 

3.1158.市场分析I

i.

# Write your MySQL query statement below
SELECT
   Users.user_id buyer_id,Users.join_date,IFNULL(UserBuy.cnt,0) orders_in_2019
FROM
   Users LEFT JOIN (SELECT buyer_id,COUNT(buyer_id) cnt 
                    FROM Orders 
                    WHERE year(order_date) = 2019
                    GROUP BY buyer_id) UserBuy 
   ON Users.user_id = UserBuy.buyer_id

ii.

# Write your MySQL query statement below
SELECT
   u.user_id buyer_id,u.join_date,COUNT(o.buyer_id) orders_in_2019
FROM
   Users u LEFT JOIN Orders o ON u.user_id = o.buyer_id AND year(o.order_date) = 2019
GROUP BY u.user_id;