通知 网站从因情语写改为晴雨,这个网站的模板也从calmlog_ex改为 whimurmur

leetcode数据库 系列题 销售分析

377人浏览 / 0人评论 / | 作者:因情语写  | 分类: 数据库  | 标签: 数据库  /  leetcode

作者:因情语写

链接:https://www.qingyu.blue/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-012019-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)


自己写的文章声明标题示例:


点赞(0) 打赏

全部评论

还没有评论!