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年春季销售,其他两个产品在之后销售。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
审题:查询固定时间段发布的产品。
思考:通过时间段判断,找出那个时间段的手机,然后对应产品表,找到产品。(思考错误,少考虑了仅字)
解题:
先找出第一季度卖的产品:between and
(
select distinct S.product_id
from Sales as S
where (S.sale_date between '2019-01-01' and '2019-03-31')
) as A
再找出其它季度卖的产品:
(
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
从第一季度卖的产品中排除掉其它季度卖过的产品。
用表A left join 表B,计算集合差。
select P.product_id,P.product_name
from
Product as P
join
(
select distinct S.product_id
from Sales as S
where (S.sale_date between '2019-01-01' and '2019-03-31')
) 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
解法二
先选出所有售卖过的产品。
select *
from
Product as P join Sales as A
on(P.product_id =A.product_id)
选出在其它季度售卖过的产品。
(
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
再从所有售卖过的产品中排除掉其它季度售卖过的产品。
用left join做集合差。
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
知识点: