Leetcode力扣 MySQL数据库 1083 销售分析II

1083 销售分析II

SQL架构

Create table If Not Exists Product_1083 (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales_1083 (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);
Truncate table Product_1083;
insert into Product_1083 (product_id, product_name, unit_price) values ('1', 'S8', '1000');
insert into Product_1083 (product_id, product_name, unit_price) values ('2', 'G4', '800');
insert into Product_1083 (product_id, product_name, unit_price) values ('3', 'iPhone', '1400');
Truncate table Sales;
insert into Sales_1083 (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales_1083 (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales_1083 (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '1', '3', '2019-06-02', '1', '800');
insert into Sales_1083 (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '3', '2019-05-13', '2', '2800');

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 S.buyer_id
FROM Sales_1083 S 
JOIN Product_1083 P ON S.product_id = P.product_id
GROUP BY S.buyer_id
HAVING 
COUNT(IF(P.product_name = 'S8',True, NULL)) > 0 AND COUNT(IF(P.product_name = 'iPhone',1, NULL)) < 1;

方法二:

select s.buyer_id
from Sales_1083 s 
    join Product_1083 p using(product_id)
group by s.buyer_id
having sum(product_name='S8')>0 and sum(product_name='iPhone')<1;