如何筛选2013年1月有订单且同年其他月也有订单的用户姓氏

需要筛选出同时符合以下两个条件的用户姓氏：

• 2013年1月至少有一笔订单
• 2013年1月以外的其他月份至少有一笔订单

以下是几种符合要求（仅使用子查询、JOIN，不使用WITH）的实现方案：

方案一：双IN子查询取交集 #

通过两个独立子查询分别筛选出满足单个条件的用户ID集合，再取交集关联用户表获取姓氏：

SELECT c.last_name
FROM client c
WHERE c.customer_id IN (
    -- 筛选2013年1月有订单的用户ID
    SELECT customer_id
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
      AND EXTRACT(MONTH FROM invoice_date::timestamp) = 1
    GROUP BY customer_id
    HAVING COUNT(invoice_id) >= 1
)
AND c.customer_id IN (
    -- 筛选2013年其他月份有订单的用户ID
    SELECT customer_id
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
      AND EXTRACT(MONTH FROM invoice_date::timestamp) != 1
    GROUP BY customer_id
    HAVING COUNT(invoice_id) >= 1
);

方案二：分组统计后JOIN关联 #

先对订单表按用户分组，统计用户的订单月份情况，筛选出同时包含1月和其他月份的用户，再关联用户表：

SELECT c.last_name
FROM client c
JOIN (
    SELECT customer_id
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
    GROUP BY customer_id
    -- 两个条件：至少有1月的订单，且订单涉及多个月份
    HAVING SUM(CASE WHEN EXTRACT(MONTH FROM invoice_date::timestamp) = 1 THEN 1 ELSE 0 END) >= 1
       AND COUNT(DISTINCT EXTRACT(MONTH FROM invoice_date::timestamp)) > 1
) AS qualified_customers ON c.customer_id = qualified_customers.customer_id;

方案三：双EXISTS子查询验证 #

用两个EXISTS子查询分别验证用户是否满足两个条件，这种方式在数据库中通常有较好的查询性能：

SELECT c.last_name
FROM client c
WHERE EXISTS (
    -- 验证用户2013年1月有订单
    SELECT 1
    FROM invoice i1
    WHERE i1.customer_id = c.customer_id
      AND EXTRACT(YEAR FROM i1.invoice_date::timestamp) = 2013
      AND EXTRACT(MONTH FROM i1.invoice_date::timestamp) = 1
)
AND EXISTS (
    -- 验证用户2013年其他月份有订单
    SELECT 1
    FROM invoice i2
    WHERE i2.customer_id = c.customer_id
      AND EXTRACT(YEAR FROM i2.invoice_date::timestamp) = 2013
      AND EXTRACT(MONTH FROM i2.invoice_date::timestamp) != 1
);

内容的提问来源于stack exchange，提问作者fujidaon