【LeetCode-SQL专项突破】-第9天:控制流

🚀 Stocks 表：+---------------+---------+| Column Name   | Type    |+---------------+---------+| stock_name    | varchar || operation     | enum    || operation_day | int     || price         | int     |+---------------+---------+(stock_name, day) 是这张表的主键operation 列使用的是一种枚举类型，包括：('Sell','Buy')此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。保证股票的每次'Sell'操作前，都有相应的'Buy'操作。🚀 需求编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。SQL查询结果的格式如下例所示：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  |+---------------+-----------+---------------+--------+Result 表:+---------------+-------------------+| stock_name    | capital_gain_loss |+---------------+-------------------+| Corona Masks  | 9500              || Leetcode      | 8000              || Handbags      | -23000            |+---------------+-------------------+Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy'->'Sell'）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。🐴🐴 答案# Write your MySQL query statement belowselect stock_name,sum(case when operation='Sell' then price else 0 end)-sum(case when operation='Buy' then price else 0 end)capital_gain_lossfrom Stocksgroup by stock_name/* Write your T-SQL query statement below */select stock_name,sum(case when operation='buy' then -priceelse  price  end ) as 'capital_gain_loss'from Stocksgroup by stock_name/* Write your PL/SQL query statement below */select stock_name "stock_name",sum(    case    when operation = 'Sell' then price    when operation = 'Buy'  then -price    end) as "capital_gain_loss"from Stocksgroup by stock_name

🚀表：Users+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || name          | varchar |+---------------+---------+id 是该表单主键。name 是用户名字。表：Rides+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || user_id       | int     || distance      | int     |+---------------+---------+id 是该表单主键。user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。🚀 需求写一段 SQL , 报告每个用户的旅行距离。返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。查询结果格式如下例所示。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      |+------+----------+----------+Result 表：+----------+--------------------+| name     | travelled_distance |+----------+--------------------+| Elvis    | 450                || Lee      | 450                || Bob      | 317                || Jonathan | 312                || Alex     | 222                || Alice    | 120                || Donald   | 0                  |+----------+--------------------+Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。Donald 没有任何行程, 他的旅行距离为 0。🐴🐴 答案# Write your MySQL query statement belowselect max(name) name,ifnull(sum(distance),0) travelled_distancefrom Users u left join Rides r on r.user_id=u.idgroup by r.user_idorder by travelled_distance desc,name/* Write your T-SQL query statement below */select max(name) name,isnull(sum(distance),0) travelled_distancefrom Users u left join Rides r on r.user_id=u.idgroup by r.user_idorder by travelled_distance desc,name/* Write your PL/SQL query statement below */select max(name) "name",sum(nvl(distance,0)) "travelled_distance"from Users u left join Rides r on r.user_id=u.idgroup by r.user_idorder by 2 desc,1

🚀 Table: Users+----------------+---------+| Column Name    | Type    |+----------------+---------+| user_id        | int     || join_date      | date    || favorite_brand | varchar |+----------------+---------+此表主键是 user_id。表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。Table: Orders+---------------+---------+| Column Name   | Type    |+---------------+---------+| order_id      | int     || order_date    | date    || item_id       | int     || buyer_id      | int     || seller_id     | int     |+---------------+---------+此表主键是 order_id。外键是 item_id 和（buyer_id，seller_id）。Table: Items+---------------+---------+| Column Name   | Type    |+---------------+---------+| item_id       | int     || item_brand    | varchar |+---------------+---------+此表主键是 item_id。🚀 需求请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。以 任意顺序 返回结果表。查询结果格式如下。示例 1:输入：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         |+---------+------------+输出：+-----------+------------+----------------+| buyer_id  | join_date  | orders_in_2019 |+-----------+------------+----------------+| 1         | 2018-01-01 | 1              || 2         | 2018-02-09 | 2              || 3         | 2018-01-19 | 0              || 4         | 2018-05-21 | 0              |+-----------+------------+----------------+🐴🐴 答案# Write your MySQL query statement belowSELECT user_id AS buyer_id, join_date, IFNULL(Buy.cnt, 0) AS orders_in_2019FROM UsersLEFT JOIN (    SELECT buyer_id, COUNT(order_id) AS cnt     FROM Orders    WHERE Year(order_date)='2019'    GROUP BY buyer_id) AS BuyON user_id=Buy.buyer_id/* Write your T-SQL query statement below */SELECT user_id AS buyer_id, join_date, isnull(Buy.cnt,0) AS orders_in_2019FROM UsersLEFT JOIN (    SELECT buyer_id, COUNT(order_id) AS cnt     FROM Orders    WHERE Year(order_date)='2019'    GROUP BY buyer_id)  BuyON user_id=Buy.buyer_id/* Write your PL/SQL query statement below */select buyer_id "buyer_id",to_char(max(join_date),'yyyy-mm-dd') "join_date",count(case when to_number(to_char(order_date,'yyyy')) = 2019 then order_idelse null end) "orders_in_2019"from Users a,Orders bwhere a.user_id = b. buyer_idgroup by buyer_idorder by 1