如何筛选所有订单状态均为Cancelled的客户ID？

原SQL存在两个核心问题：一是用=匹配子查询结果，子查询返回多个ID时会报错；二是仅筛选出有取消订单的客户，未排除同时存在其他状态订单的客户。以下是两种可行的修正方案：

方案一：使用NOT EXISTS排除非取消订单的客户 #

SELECT
   oc.customer_id
  ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  ,oc.customer_email
  ,oc.customer_phone
  ,a.country
FROM online_customer oc
JOIN address a ON oc.address_id = a.address_id
WHERE NOT EXISTS (
  SELECT 1
  FROM order_header oh
  WHERE oh.customer_id = oc.customer_id
    AND oh.order_status != 'Cancelled'
)
-- 若需排除无任何订单的客户，添加以下条件
AND EXISTS (
  SELECT 1
  FROM order_header oh
  WHERE oh.customer_id = oc.customer_id
);

逻辑说明：通过NOT EXISTS判断当前客户是否存在非"Cancelled"状态的订单，不存在则符合要求；第二个EXISTS用于过滤无订单的客户，可根据需求选择是否保留。

方案二：分组统计验证所有订单均为取消状态 #

SELECT
   oc.customer_id
  ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  ,oc.customer_email
  ,oc.customer_phone
  ,a.country
FROM online_customer oc
JOIN address a ON oc.address_id = a.address_id
JOIN (
  SELECT customer_id
  FROM order_header
  GROUP BY customer_id
  HAVING COUNT(*) = SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END)
) oh ON oc.customer_id = oh.customer_id;

逻辑说明：子查询分组统计每个客户的总订单数，以及状态为"Cancelled"的订单数，当两者相等时，说明该客户所有订单均已取消。

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