1045. 买下所有产品的客户
思路
- 在Customer表中计算每个客户买Product的种类，为防重复购买，COUNT()要配合DISTINCT食用。创建临时表a。
FROM (SELECT customer_id, COUNT(DISTINCT product_key) AS num FROM Customer GROUP BY customer_id) a
- 计算Product表中的类型数。创建临时表b。
(SELECT COUNT(product_key) AS num FROM Product) b
3.用自联结(JOIN取交集)
SELECT customer_id
FROM (SELECT customer_id, COUNT(DISTINCT product_key) AS num FROM Customer GROUP BY customer_id) a
JOIN
(SELECT COUNT(product_key) AS num FROM Product) b
ON a.num = b.num;
603. 连续空余座位
自连接
select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b;
找到两个连续的空的座位
select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b
on abs(a.seat_id - b.seat_id) = 1
and a.free = true and b.free = true;
最终代码
SELECT DISTINCT a.seat_id
FROM cinema a JOIN cinema b
ON abs(a.seat_id - b.seat_id) = 1
AND a.free = TRUE AND b.free = TRUE
ORDER BY a.seat_id;