Day01-LeetCode-SQL启航~

大家好，我是一位程式菜鸟，这边将会分享我刷题的过程与遇到的问题，
第一站是SQL!!会先附上题目、遇到的问题，最后是该题的程式码。

595. Big Countries

+-------------+---------+| Column Name | Type    |+-------------+---------+| name        | varchar || continent   | varchar || area        | int     || population  | int     || gdp         | int     |+-------------+---------+name is the primary key column for this table.Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.A country is big if:it has an area of at least three million (i.e., 3000000 km2), orit has a population of at least twenty-five million (i.e., 25000000).Write an SQL query to report the name, population, and area of the big countries.Return the result table in any order.The query result format is in the following example. Example 1:Input: World table:+-------------+-----------+---------+------------+--------------+| name        | continent | area    | population | gdp          |+-------------+-----------+---------+------------+--------------+| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  || Albania     | Europe    | 28748   | 2831741    | 12960000000  || Algeria     | Africa    | 2381741 | 37100000   | 188681000000 || Andorra     | Europe    | 468     | 78115      | 3712000000   || Angola      | Africa    | 1246700 | 20609294   | 100990000000 |+-------------+-----------+---------+------------+--------------+Output: +-------------+------------+---------+| name        | population | area    |+-------------+------------+---------+| Afghanistan | 25500100   | 652230  || Algeria     | 37100000   | 2381741 |+-------------+------------+---------+

如果像我一样英文不好就直接丢Google翻译，题意是要我们筛选出
**面积至少为三百万（即 3000000 平方公里），或
至少有 2500 万人口（即 2500 万）。

SELECT Name, Population, Area  /*选需要的栏位*/FROM WorldWHERE population >= 25000000 orarea >= 3000000;   /*列出筛选条件*/

P.S.一开始遇到的问题是leetcode Run时会通过，提交时会出错，
花了些时间爬文发现筛选条件 ">=" 这边不能只写 ">" ，一定要 ">=" 才会通过。
下面是别的大神分享用"UNION"能查更快，
原理是将这两个结果合併，省去重新程式查询的时间。

SELECT name, population, areaFROM worldWHERE area >= 3000000UNIONSELECT name, population, areaFROM worldWHERE population >= 25000000;

1757. Recyclable and Low Fat Products

+-------------+---------+| Column Name | Type    |+-------------+---------+| product_id  | int     || low_fats    | enum    || recyclable  | enum    |+-------------+---------+product_id is the primary key for this table.low_fats is an ENUM of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is not.recyclable is an ENUM of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means it is not. Write an SQL query to find the ids of products that are both low fat and recyclable.Return the result table in any order.The query result format is in the following example.Example 1:Input: Products table:+-------------+----------+------------+| product_id  | low_fats | recyclable |+-------------+----------+------------+| 0           | Y        | N          || 1           | Y        | Y          || 2           | N        | Y          || 3           | Y        | Y          || 4           | N        | N          |+-------------+----------+------------+Output: +-------------+| product_id  |+-------------+| 1           || 3           |+-------------+Explanation: Only products 1 and 3 are both low fat and recyclable.

**题目希望我们列出同时'低脂'与'可回收'的产品ID
从表格输出能看到共同点，栏位"low_fats"跟"recyclable"都是"Y"的情况

SELECT product_id FROM ProductsWHERE low_fats='Y' AND recyclable='Y'; /*用AND表示要同时符合这两个条件*/

584. Find Customer Referee

+-------------+---------+| Column Name | Type    |+-------------+---------+| id          | int     || name        | varchar || referee_id  | int     |+-------------+---------+id is the primary key column for this table.Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.Return the result table in any order.The query result format is in the following example.Example 1:Input: Customer table:+----+------+------------+| id | name | referee_id |+----+------+------------+| 1  | Will | null       || 2  | Jane | null       || 3  | Alex | 2          || 4  | Bill | null       || 5  | Zack | 1          || 6  | Mark | 2          |+----+------+------------+Output: +------+| name |+------+| Will || Jane || Bill || Zack |+------+

**这题要求找出"referee_id"不是2的名字，如果直接写WHERE referee_id !=2;会被判定不通过，
要让referee_id的值不为NULL 提交才会判定通过，解法是用ifnull<<让里面的null=1才能顺利提交。
P.S. ifnull这个函式是在问Google为什么Run ok， Submit却失败时知道的解法。

SELECT name FROM Customer WHERE ifnull(referee_id,1) !=2; /*用ifnull使null值为1*/

183. Customers Who Never Order

+-------------+---------+| Column Name | Type    |+-------------+---------+| id          | int     || name        | varchar |+-------------+---------+id is the primary key column for this table.Each row of this table indicates the ID and name of a customer. Table: Orders+-------------+------+| Column Name | Type |+-------------+------+| id          | int  || customerId  | int  |+-------------+------+id is the primary key column for this table.customerId is a foreign key of the ID from the Customers table.Each row of this table indicates the ID of an order and the ID of the customer who ordered it.Write an SQL query to report all customers who never order anything.Return the result table in any order.The query result format is in the following example.Example 1:Input: Customers table:+----+-------+| id | name  |+----+-------+| 1  | Joe   || 2  | Henry || 3  | Sam   || 4  | Max   |+----+-------+Orders table:+----+------------+| id | customerId |+----+------------+| 1  | 3          || 2  | 1          |+----+------------+Output: +-----------+| Customers |+-----------+| Henry     || Max       |+-----------+

**这题是要列出没有购买的客户名字，由于有Customers、Orders两个表，
需要先关联表单后再做筛选，看到大神有用AS来做命名，就学着做了。
底下会注解大神的思路。

SELECT TB1.NAME AS Customers /*5.最后输出TB1.NAME栏位改称Customers*/FROM Customers AS TB1        /*1.让Customers改称TB1*/LEFT JOIN Orders AS TB2      /*2.关联Orders并让Orders改称TB2*/ON TB1.Id = TB2.customerId   /*3.用ON表示TB2的customerId要加入TB1的Id栏*/WHERE TB2.Id IS NULL;        /*4.条件为TB2的Id栏是NULL，表示没订购过*/

P.S.我自己最后写成WHERE TB2.customerId IS NULL <<用customerId的空值也行，
可以想像TB2是连在TB1右侧，用户Joe、Sam他们有订购纪录的值，找NULL自然就能筛出Henry、Max。

最后谢谢各位看到这边，希望新手在LeetCode 584、595这两题遇到Run ok却Submit错误时也能避开这些时间成本。