Leetcode1084. 销售分析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年春季销售，其他两个产品在之后销售。

解答
连接两表 选出在2019年春季销售的产品 tmp1

SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` BETWEEN '2019-01-01' AND '2019-03-31';

连接两表 选出不在2019年春季销售的产品 tmp2

SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` < '2019-01-01' OR S.`sale_date` > '2019-03-31';

选出tmp1中不在tmp2中的集合

SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` BETWEEN '2019-01-01' AND '2019-03-31' AND 
(P.`product_id`, P.`product_name`) NOT IN(SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` < '2019-01-01' OR S.`sale_date` > '2019-03-31');

用集合差也可行

从第一季度卖的产品中排除掉其它季度卖过的产品。

SELECT tmp1.`product_id`, tmp1.`product_name`
FROM (SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` BETWEEN '2019-01-01' AND '2019-03-31') tmp1
LEFT JOIN (SELECT P.`product_id`, P.`product_name`
FROM product AS P
JOIN sales AS S
ON P.`product_id` = S.`product_id` 
WHERE S.`sale_date` < '2019-01-01' OR S.`sale_date` > '2019-03-31') tmp2
ON tmp1.`product_id` = tmp2.`product_id`
WHERE tmp2.`product_id` IS NULL;

从所有售卖过的产品中排除掉其它季度售卖过的产品。

select distinct P.product_id,P.product_name
from
Product as P join Sales as A
    on(P.product_id =A.product_id)
left join 
(
    select distinct S.product_id
    from Sales as S
    where (S.sale_date < '2019-01-01' or S.sale_date > '2019-03-31')
)as B
    on (A.product_id = B.product_id)
where B.product_id is NULL