Day08-LeetCode-SQL启航~

LEETCODE SQL挑战超过时间只能重新等，
但发现可以先点出题目来写

第一题一直被判定成含有禁止字元，改用截图的方式呈现。

1393. Capital Gain/Loss

这一题要算股票买卖的收益，最快的理解方法就是直接看例子，
Corona Masks 买了1000元与卖了9000元，赚了8000，
可以看成BUY就是-1000，SELL是+9000
P.S.SUM(IF(operation ='Sell',price,0))结构是SUM( IF() ,THEN() ,ELSE() )

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    

1407. Top Travellers

+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || name          | varchar |+---------------+---------+id is the primary key for this table.name is the name of the user.Table: Rides+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || user_id       | int     || distance      | int     |+---------------+---------+id is the primary key for this table.user_id is the id of the user who traveled the distance "distance".Write an SQL query to report the distance traveled by each user.Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.The query result format is in the following example.Example 1:Input: Users table:+------+-----------+| id   | name      |+------+-----------+| 1    | Alice     || 2    | Bob       || 3    | Alex      || 4    | Donald    || 7    | Lee       || 13   | Jonathan  || 19   | Elvis     |+------+-----------+Rides table:+------+----------+----------+| 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      |+------+----------+----------+Output: +----------+--------------------+| name     | travelled_distance |+----------+--------------------+| Elvis    | 450                || Lee      | 450                || Bob      | 317                || Jonathan | 312                || Alex     | 222                || Alice    | 120                || Donald   | 0                  |+----------+--------------------+Explanation: Elvis and Lee traveled 450 miles, Elvis is the top traveler as his name is alphabetically smaller than Lee.Bob, Jonathan, Alex, and Alice have only one ride and we just order them by the total distances of the ride.Donald did not have any rides, the distance traveled by him is 0.

这一题要计算旅行的里程，跑越多距离的排越上面(降序)，
距离一样则依名字排(升序)，ifnull之前有写到过，遇到NULL就把它变成0处理，
ORDER BY 2 DESC, 1的结构是SELECT 1,2去表示的，
就不用打成ORDER BY travelled_distance DESC, name

SELECT    name,    ifnull(sum(distance),0) AS travelled_distanceFROM Users u LEFT JOIN Rides rON r.user_id = u.id GROUP BY u.idORDER BY 2 DESC, 1

1158. Market Analysis I

+----------------+---------+| Column Name    | Type    |+----------------+---------+| user_id        | int     || join_date      | date    || favorite_brand | varchar |+----------------+---------+user_id is the primary key of this table.This table has the info of the users of an online shopping website where users can sell and buy items. Table: Orders+---------------+---------+| Column Name   | Type    |+---------------+---------+| order_id      | int     || order_date    | date    || item_id       | int     || buyer_id      | int     || seller_id     | int     |+---------------+---------+order_id is the primary key of this table.item_id is a foreign key to the Items table.buyer_id and seller_id are foreign keys to the Users table.Table: Items+---------------+---------+| Column Name   | Type    |+---------------+---------+| item_id       | int     || item_brand    | varchar |+---------------+---------+item_id is the primary key of this table. Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.Return the result table in any order.The query result format is in the following example.Example 1:Input: Users table:+---------+------------+----------------+| 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 table:+----------+------------+---------+----------+-----------+| 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 table:+---------+------------+| item_id | item_brand |+---------+------------+| 1       | Samsung    || 2       | Lenovo     || 3       | LG         || 4       | HP         |+---------+------------+Output: +-----------+------------+----------------+| 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              |+-----------+------------+----------------+

这一题要找出各厂商2019的订单数，同时还要列出注册日，
找2019直觉就用LIKE "2019%"，到order_date找2019开头的订单，
需要熟练的地方是LEFT JOIN ON这个关联的用法，真的很常用到，
还有只要有用COUNT这类数学函式，就要用GROUP BY做排序。

SELECT U.user_id AS buyer_id  ,join_date  ,COUNT(order_date) AS orders_in_2019 FROM Users ULEFT JOIN Orders O ON U.user_id=O.buyer_id AND O.order_date LIKE "2019%"GROUP BY U.user_id