LeetCode数据库SQL练习（十一）练习题目来自：https://leetcode-cn.com/ 股票的资本损益

练习题目来自：leetcode-cn.com/

股票的资本损益

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美元。

条件判断:

SQL1：

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

SQL2：

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

SQL3：

窗口函数

select
distinct t1.stock_name,
sum(t2.p2 - t1.p1) capital_gain_loss
from
    (select
    stock_name, price p1,
    ROW_NUMBER() over(partition by stock_name order by operation_day) rn1
    from
    Stocks
    where operation = 'Buy') t1
    join
    (select
    stock_name, price p2,
    ROW_NUMBER() over(partition by stock_name order by operation_day) rn2
    from
    Stocks
    where operation = 'Sell') t2
on t1.stock_name = t2.stock_name and t1.rn1 = t2.rn2
group by stock_name

购买了产品 A 和产品 B 却没有购买产品 C 的顾客

Customers 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。

Orders 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ），我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id 排序。

查询结果如下例所示。

Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+

Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

SQL1：

子查询

select
*
from
Customers
where customer_id in (select
                     customer_id
                     from
                     Customers
                     where customer_id in (select
                                          customer_id
                                          from
                                          Orders
                                          where product_name = 'A')
                       and customer_id in (select
                                          customer_id
                                          from
                                          Orders
                                          where product_name = 'B')
                       and customer_id not in (select
                                              customer_id
                                              from
                                              Orders
                                              where product_name = 'C'))

SQL2：

Join方法

select
o.customer_id, c.customer_name
from
Customers c join Orders o
using(customer_id)
group by o.customer_id
having count(if(o.product_name = 'A', 1, null)) > 0
       and count(if(o.product_name = 'B', 1, null)) > 0
       and count(if(o.product_name = 'C', 1, null)) = 0

SQL3：

设定值加和

select
*
from
Customers
where customer_id in (select
                     customer_id
                     from
                     (select
                      customer_id,
                      (case
                      when product_name='A' then 100
                      when product_name='B' then 10
                      when product_name='C' then 1
                      end) product_num
                      from
                      Orders) t
group by customer_id
having sum(distinct product_num) = 110)

排名靠前的旅行者

表：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。

SQL：

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

查找成绩处于中游的学生

表: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键.
student_name 学生名字.

表: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键.
学生 student_id 在测验 exam_id 中得分为 score.

成绩处于中游的学生是指至少参加了一次测验,且得分既不是最高分也不是最低分的学生。
写一个 SQL 语句，找出在所有测验中都处于中游的学生 (student_id, student_name)。
不要返回从来没有参加过测验的学生。返回结果表按照student_id排序。

查询结果格式如下。

Student 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+

Exam 表：
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+

Result 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+

对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

SQL1：

子查询

select
distinct e.student_id, student_name
from Exam e join Student s
using(student_id)
where student_id not in (select
                        e1.student_id
                        from
                        Exam e1 join Exam e2
                        using(exam_id)
                        group by e1.exam_id, e1.student_id
                        having sum(e1.score < e2.score) = 0 or sum(e1.score > e2.score) = 0)
order by e.student_id

窗口函数：

SQL2：

select
t.student_id,s.student_name
from
(select
*,
if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) dr1,
if(dense_rank() over(partition by exam_id order by score )=1,1,0) dr2
from Exam) t
left join Student s
using(student_id)
group by t.student_id
having sum(dr1) = 0 and sum(dr2) = 0
order by student_id

SQL3：

select
student_id, student_name
from
Student
where student_id in (select
                      student_id
                      from
                      (select
                      *,
                      max(score) over(partition by exam_id) max_score,
                      min(score) over(partition by exam_id) min_score
                      from
                      Exam) t
group by student_id
having sum(if(t.score > min_score and t.score < max_score, 1, 0)) = count(distinct exam_id))
order by student_id

净现值查询

表: NPV

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
| npv           | int     |
+---------------+---------+
(id, year) 是该表主键.
该表有每一笔存货的年份, id 和对应净现值的信息.

表: Queries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
+---------------+---------+
(id, year) 是该表主键.
该表有每一次查询所对应存货的 id 和年份的信息.

写一个 SQL, 找到 Queries 表中每一次查询的净现值.
结果表没有顺序要求.

查询结果的格式如下所示:

NPV 表:
+------+--------+--------+
| id   | year   | npv    |
+------+--------+--------+
| 1    | 2018   | 100    |
| 7    | 2020   | 30     |
| 13   | 2019   | 40     |
| 1    | 2019   | 113    |
| 2    | 2008   | 121    |
| 3    | 2009   | 12     |
| 11   | 2020   | 99     |
| 7    | 2019   | 0      |
+------+--------+--------+

Queries 表:
+------+--------+
| id   | year   |
+------+--------+
| 1    | 2019   |
| 2    | 2008   |
| 3    | 2009   |
| 7    | 2018   |
| 7    | 2019   |
| 7    | 2020   |
| 13   | 2019   |
+------+--------+

结果表:
+------+--------+--------+
| id   | year   | npv    |
+------+--------+--------+
| 1    | 2019   | 113    |
| 2    | 2008   | 121    |
| 3    | 2009   | 12     |
| 7    | 2018   | 0      |
| 7    | 2019   | 0      |
| 7    | 2020   | 30     |
| 13   | 2019   | 40     |
+------+--------+--------+

(7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0.
所有其它查询的净现值都能在 NPV 表中找到.

SQL：

select
q.id, q.year, ifnull(n.npv, 0) npv
from
Queries q left join NPV n
using(id, year)

制作会话柱状图

表：Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位

你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 "[0-5>", "[5-10>", "[10-15>" 和 "15 or more" （单位：分钟）的会话数量，并以此绘制柱状图。

写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现。

下方为查询的输出格式：

Sessions 表：
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+

Result 表：
+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+

对于 session_id 1，2 和 3 ，它们的访问时间大于等于 0 分钟且小于 5 分钟。
对于 session_id 4，它的访问时间大于等于 5 分钟且小于 10 分钟。
没有会话的访问时间大于等于 10 分钟且小于 15 分钟。
对于 session_id 5, 它的访问时间大于等于 15 分钟。

SQL1：

select
t1.bin, ifnull(t2.total, 0) total
from
(select
'[0-5>' bin
union all
select
'[5-10>' bin
union all
select
'[10-15>' bin
union all
select
'15 or more' bin) t1
left join
(select
t.bin, count(t.bin) total
from
    (select
    session_id,
    case
    when duration / 60 >= 0 and duration / 60 < 5 then '[0-5>'
    when duration / 60 >= 5 and duration / 60 < 10 then '[5-10>'
    when duration / 60 >= 10 and duration / 60 < 15 then '[10-15>'
    when duration / 60 >= 15 then '15 or more'
    end bin
    from
    Sessions) t
group by t.bin) t2
using(bin)

SQL2：

select
'[0-5>' bin, count(*) total
from
Sessions
where duration / 60 >= 0 and duration / 60 < 5
union
select
'[5-10>' bin, count(*) total
from
Sessions
where duration / 60 >= 5 and duration / 60 < 10
union
select
'[10-15>' bin, count(*) total
from
Sessions
where duration / 60 >= 10 and duration / 60 < 15
union
select
'15 or more' bin, count(*) total
from
Sessions
where duration / 60 >= 15

计算布尔表达式的值

表 Variables:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
name 是该表主键.
该表包含了存储的变量及其对应的值.

表 Expressions:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.

写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式.
返回的结果表没有顺序要求.

查询结果格式如下例所示：

Variables 表:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+

Expressions 表:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+

Result 表:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.

SQL：

select
e.left_operand, e.operator, e.right_operand,
(case
when v1.value > v2.value and e.operator = '>' then 'true'
when v1.value = v2.value and e.operator = '=' then 'true'
when v1.value < v2.value and e.operator = '<' then 'true'
else 'false'
end) value
from Expressions e
left join Variables v1
on e.left_operand = v1.name
left join Variables v2
on e.right_operand = v2.name

苹果和桔子

表: Sales

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    | 
| sold_num      | int     | 
+---------------+---------+
(sale_date,fruit) 是该表主键.
该表包含了每一天中"苹果" 和 "桔子"的销售情况.

写一个 SQL 查询, 报告每一天苹果和桔子销售的数目的差异.
返回的结果表, 按照格式为 ('YYYY-MM-DD') 的 sale_date 排序.

查询结果表如下例所示:

Sales 表:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+

Result 表:
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+

在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).

SQL1：

select
t1.sale_date, (t1.sold_num - t2.sold_num) diff
from
    (select
    sale_date, sold_num,
    ROW_NUMBER() over(order by sale_date) rn1
    from
    Sales
    where fruit = 'apples') t1
    join
    (select
    sale_date, sold_num,
    ROW_NUMBER() over(order by sale_date) rn2
    from
    Sales
    where fruit = 'oranges') t2
on t1.sale_date = t2.sale_date and t1.rn1 = t2.rn2
order by date(t1.sale_date)

SQL2：

select
sale_date, (Sum_a - Sum_o) diff
from
    (select
    sale_date,
    sum(case when fruit = 'apples' then sold_num else 0 end) Sum_a,
    sum(case when fruit = 'oranges' then sold_num else 0 end) Sum_o
    from Sales
    group by sale_date) t
order by sale_date

SQL3：

select
sale_date,
sum(if(fruit = 'apples', 1, -1) * sold_num) diff
from
Sales
group by sale_date
order by sale_date

活跃用户

表 Accounts:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.

表 Logins:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

写一个 SQL 查询, 找到活跃用户的 id 和 name.
活跃用户是指那些至少连续5 天登录账户的用户.
返回的结果表按照 id 排序.

结果表格式如下例所示:

Accounts 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+

Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+

Result 表:
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

进阶问题:
如果活跃用户是那些至少连续n天登录账户的用户,你能否写出通用的解决方案?

SQL1：

自连接

select
distinct a.*
from
Accounts a
join Logins l1
using(id)
join logins l2
on l1.id = l2.id and datediff(l2.login_date, l1.login_date) between 0 and 4
group by a.id, a.name, l1.login_date
having count(distinct l2.login_date) = 5

窗口函数：

SQL2：

select
distinct id, name
from
    (select
    id,
    subdate(login_date, rn) d1
    from
        (select
        id, login_date,
        row_number() over(partition by id order by login_date) rn
        from
            (select
            id, login_date
            from Logins
            group by id, login_date
            order by id, login_date) t1) t2
        group by id, d1
        having count(*) >= 5) t3
left join Accounts
using(id)

SQL3：

select
distinct id, name
from
    (select
    id, login_date,
    datediff(LEAD(login_date, 4) over(partition by id order by login_date), login_date) diff
    from Logins
    group by id, login_date) t
left join accounts
using(id)
where diff = 4

矩形面积

表: Points

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| x_value       | int     |
| y_value       | int     |
+---------------+---------+
id 是该表主键
每个点都用二维坐标 (x_value, y_value) 表示

写一个 SQL 语句，报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
结果表中的每一行包含三列 (p1, p2, area)如下:

p1和p2是矩形两个对角的 id
矩形的面积由列area****表示

请按照面积area 大小降序排列；如果面积相同的话, 则按照p1升序排序；若仍相同，则按 p2 升序排列。

查询结果如下例所示：

Points 表:
+----------+-------------+-------------+
| id       | x_value     | y_value     |
+----------+-------------+-------------+
| 1        | 2           | 7           |
| 2        | 4           | 8           |
| 3        | 2           | 10          |
+----------+-------------+-------------+

Result 表:
+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 4           |
| 1        | 2           | 2           |
+----------+-------------+-------------+

p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |8-10| = 4
p1 = 1 且 p2 = 2 时, 面积等于 ||2-4| * |7-8| = 2 
p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 面积等于 0

SQL：

select
t.id1 p1, t.id2 p2, abs(t.x1 - t.x2) * abs(t.y1 - t.y2) area
from
  (select
  p1.id id1, p1.x_value x1, p1.y_value y1, p2.id id2, p2.x_value x2, p2.y_value y2
  from
  Points p1 join Points p2
  on p1.id < p2.id
  where
  p1.x_value != p2.x_value and p1.y_value != p2.y_value) t
order by area DESC, p1, p2

计算税后工资

Salaries 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| company_id    | int     |
| employee_id   | int     |
| employee_name | varchar |
| salary        | int     |
+---------------+---------+
(company_id, employee_id) 是这个表的主键
这个表包括员工的company id, id, name 和 salary

写一条查询 SQL 来查找每个员工的税后工资。
每个公司的税率计算依照以下规则：

如果这个公司员工最高工资不到 1000 ，税率为 0%
如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%
如果这个公司员工最高工资大于 10000 ，税率为 49%

按任意顺序返回结果，税后工资结果取整。

结果表格式如下例所示：

Salaries 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1          | 1           | Tony          | 2000   |
| 1          | 2           | Pronub        | 21300  |
| 1          | 3           | Tyrrox        | 10800  |
| 2          | 1           | Pam           | 300    |
| 2          | 7           | Bassem        | 450    |
| 2          | 9           | Hermione      | 700    |
| 3          | 7           | Bocaben       | 100    |
| 3          | 2           | Ognjen        | 2200   |
| 3          | 13          | Nyancat       | 3300   |
| 3          | 15          | Morninngcat   | 7777   |
+------------+-------------+---------------+--------+

Result 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1          | 1           | Tony          | 1020   |
| 1          | 2           | Pronub        | 10863  |
| 1          | 3           | Tyrrox        | 5508   |
| 2          | 1           | Pam           | 300    |
| 2          | 7           | Bassem        | 450    |
| 2          | 9           | Hermione      | 700    |
| 3          | 7           | Bocaben       | 76     |
| 3          | 2           | Ognjen        | 1672   |
| 3          | 13          | Nyancat       | 2508   |
| 3          | 15          | Morninngcat   | 5911   |
+------------+-------------+---------------+--------+
对于公司 1 ，最高工资是 21300 ，其每个员工的税率为 49%
对于公司 2 ，最高工资是 700 ，其每个员工税率为 0%
对于公司 3 ，最高工资是 7777 ，其每个员工税率是 24%
税后工资计算 = 工资 - ( 税率 / 100）*工资
对于上述案例，Morninngcat 的税后工资 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 ，取整为 5911

SQL1：

select
company_id, employee_id, employee_name,
round(case
when max < 1000 then salary
when max >= 1000 and max <= 10000 then salary - (0.24) * salary
when max > 10000 then salary - (0.49) * salary
end, 0) salary
from
    (select
    s.*, t.max
    from
    (select
    company_id, max(salary) max
    from
    Salaries
    group by company_id) t
    right join Salaries s
    using(company_id)) t1

SQL2：

select
s.company_id, s.employee_id, s.employee_name,
round(s.salary * (1 - t.rate), 0) salary
from
Salaries s
left join
    (select
    company_id,
    case
    when max(salary) < 1000 then 0
    when max(salary) between 1000 and 10000 then 0.24
    when max(salary) >= 10000 then 0.49
    end rate
    from
    Salaries
    group by company_id) t
using(company_id)