作者：whisper

链接：http://proprogrammar.com:443/article/701

声明：请尊重原作者的劳动，如需转载请注明出处

1082. 销售分析 I

产品表：Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这个表的主键.

销售表：Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.

编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

查询结果格式如下所示：

Product 表：
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales 表：
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result 表：
+-------------+
| seller_id   |
+-------------+
| 1           |
| 3           |
+-------------+
Id 为 1 和 3 的销售者，销售总金额都为最高的 2800。

select seller_id
from Sales
group by seller_id
having sum(price) >= all(
    select sum(price) saleIncome
    from Sales
    group by seller_id
)

按人分组，销售金额总和比其它人的销售总金额都大的即最高的，学一下在having中使用聚集函数与子查询的用法，学一下求最值的方法（>=all)

再看另一种求最值的方法

SELECT
    seller_id
FROM
    Sales

GROUP BY
    seller_id
HAVING 
    SUM(price) = (
        SELECT
            SUM(price) AS seller_price
        FROM
            Sales
        GROUP BY
            seller_id
        ORDER BY
            seller_price DESC
        LIMIT 1 OFFSET 0
    );

这里用 group by...order by desc...limit 1求最值

1083. 销售分析 II

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这张表的主键

Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.

编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

查询结果格式如下图表示：

Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 1          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 3        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+
| buyer_id    |
+-------------+
| 1           |
+-------------+
id 为 1 的买家购买了一部 S8，但是却没有购买 iPhone，而 id 为 3 的买家却同时购买了这 2 部手机。

select distinct s.buyer_id
from Sales s inner join Product p
on s.product_id = p.product_id and p.product_name = 'S8' and buyer_id not in(
    select distinct s2.buyer_id
    from Sales s2 inner join Product p2
    on s2.product_id = p2.product_id and p2.product_name = 'iPhone'
)

思路清晰，买了S8的买家，且不属于买了iPhone的买家，学一下not in否定的用法

1084. 销售分析III

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这个表的主键

Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

查询结果格式如下所示：

Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+--------------+
| product_id  | product_name |
+-------------+--------------+
| 1           | S8           |
+-------------+--------------+
id为1的产品仅在2019年春季销售，其他两个产品在之后销售。

select p.product_id, p.product_name
from Product p
where exists(
    select 1
    from Sales
    where product_id = p.product_id and date_format(sale_date, '%Y-%m-%d') >= '2019-01-01' and date_format(sale_date, '%Y-%m-%d') <= '2019-03-31'
) and not exists(
    select 1
    from Sales
    where product_id = p.product_id and (date_format(sale_date, '%Y-%m-%d') < '2019-01-01' or date_format(sale_date, '%Y-%m-%d') > '2019-03-31')
)

思路清晰，存在指定时间段出售的情况，不存在指定时间段外出售的情况，学一下exist和not exists的用法

再看另一种解法

select product_id, product_name
from product 
where product_id not in (
    select product_id
    from sales
    where sale_date < '2019-01-01' or sale_date>'2019-03-31'
);

这里用not in否定指定时间段外的情况，那么剩下的全是指定时间段内的情况，上面exists是没必要的，因为默认情况是在表中一定是在某时间卖出了的，否定一下反面情况（双重否定等于肯定!!=true)

亲爱的读者：有时间可以点赞评论一下