mysql - SQL查询以根据其他列获取客户

Question

我有一个像这样的表:

Order ID | Customer ID | Product ID | Recipient ID
1        | 2           | 3          | 4
2        | 4           | 6          | 7
3        | 4           | 6          | 8
4        | 9           | 6          | 8
5        | 9           | 6          | 8
6        | 2           | 8          | 4
7        | 3           | 8          | 4
8        | 3           | 8          | 5

我想查询以下内容:

1. 为同一收件人购买相同礼物的顾客

   customer 9

2. 购买不同礼物的客户收件人

   customer 4

3. 购买不同礼物的客户相同的收件人

   customer 2

4. 购买了相同礼物的客户不同的收件人

   customer 4

我认为要运行的查询对于所有 4 个都非常相似，但我对如何开始感到困惑。

Answer 1

为同一收件人购买了相同礼物的客户(因此期望购买次数超过一次):

SELECT customer_id, recipient_id, Product_ID
FROM table_a
GROUP BY customer_id, recipient_id, Product_ID
HAVING COUNT(*)>1;

为不同的收件人购买了不同礼物的顾客:逻辑:

First find all unique combinations of data.

Then a count of recipients>1 means there is more then 1 recipient, and a count of products>1 means that there is more then 1 product=> So more then recipient=different recipient with more then 1 product_id=more then 1 gift, so it must have been different gifts.

SELECT customer_id, COUNT(recipient_id), COUNT(product_id)
FROM (
SELECT DISTINCT customer_id, recipient_id, product_id
FROM table_a ) b
GROUP BY customer_id
HAVING COUNT(recipient_id)>1 AND COUNT(product_id)>1;

为同一收件人购买了不同礼物的顾客:

SELECT customer_id, recipient_id, COUNT(DISTINCT product_id)
FROM table_a
GROUP BY customer_id, recipient_id
HAVING COUNT(DISTINCT product_id)>1

为不同的收件人购买相同礼物的顾客

SELECT customer_id, COUNT(DISTINCT recipient_id), product_id
FROM table_a
GROUP BY customer_id, product_id
HAVING COUNT(DISTINCT recipient_id)>1