题目描述
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 |
+-------------+
解题思路
浏览完题目,发现该题的关键在于筛选 购买了 S8 手机却没有购买 iPhone 的买家。
把这个条件数学化,就是某个买家购买S8的次数至少为1,购买iPhone的次数为0。
由此想到,先用GROUP BY 将原表依据买家信息排列,再用 COUNT+IF函数,来统计一个各个买家购买各手机的次数。
解题答案
SELECT
S.buyer_id
FROM
Sales S
JOIN Product P ON S.product_id = P.product_id
GROUP BY
S.buyer_id
HAVING
COUNT(
IF
( P.product_name = 'S8', TRUE, NULL )) >= 1
AND COUNT(
IF
( P.product_name = 'iPhone', TRUE, NULL )) = 0