PostgreSQL查询问题：客户与订单表筛选需求及现有SQL问题

问题1：筛选仅存在一条记录且该记录为ACTIVE的客户 #

需求 #

业务规则要求同一客户编号同一时间仅一条记录为ACTIVE，需找出仅含一条客户记录，且该记录Active标识为true的客户。

原SQL问题 #

原SQL错误筛选了active = false的记录，统计每个客户这类记录的数量为1，完全偏离需求——它返回的是有1条非ACTIVE记录的客户，和目标结果完全相反。

正确SQL #

SELECT customer_number, COUNT(*)
FROM customers c
GROUP BY customer_number
HAVING COUNT(*) = 1 AND MAX(active) = true;

或者更直观的写法：

SELECT customer_number
FROM customers c
GROUP BY customer_number
HAVING COUNT(*) = 1 
   AND SUM(CASE WHEN active = true THEN 1 ELSE 0 END) = 1;

问题2：筛选ACTIVE为false且180天内无订单的客户 #

需求 #

找出客户状态为false，且最近180天内没有任何订单的客户。

原SQL问题 #

1. 采用隐式内连接（customers c, order_master om），会直接过滤掉没有订单的客户，而这类客户正是我们需要包含的；
2. 子查询沿用了问题1的错误逻辑，筛选的是有1条非ACTIVE记录的客户，不符合“ACTIVE为false”的需求；
3. 使用固定日期'2022-06-01'不够灵活，无法适配动态的180天时间范围。

正确SQL #

假设每个客户在Customer表中仅有一条记录（或当前有效状态为false），使用左连接保留无订单客户：

SELECT c.customer_number, c.customer_name
FROM customers c
LEFT JOIN order_master om ON c.customer_number = om.customer_number
WHERE c.active = false
GROUP BY c.customer_number, c.customer_name
HAVING MAX(om.insert_time::date) < CURRENT_DATE - INTERVAL '180 days'
    OR MAX(om.insert_time) IS NULL;

如果Customer表中一个客户存在多条记录，需先确定客户的当前有效状态为false，可使用CTE处理：

WITH inactive_customers AS (
    SELECT customer_number
    FROM customers
    WHERE active = false
    -- 若需取客户最新状态，可添加排序逻辑，例如：
    -- ORDER BY insert_time DESC LIMIT 1
)
SELECT ic.customer_number, c.customer_name
FROM inactive_customers ic
JOIN customers c ON ic.customer_number = c.customer_number
LEFT JOIN order_master om ON ic.customer_number = om.customer_number
GROUP BY ic.customer_number, c.customer_name
HAVING MAX(om.insert_time::date) < CURRENT_DATE - INTERVAL '180 days'
    OR MAX(om.insert_time) IS NULL;

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