如何结合CASE语句、变量与GROUP BY筛选指定条件客户ID

以下是整合了变量、CASE语句和GROUP BY的查询代码，满足你筛选customer_id>2且状态为Not Created的客户需求：

DECLARE @Match_status VARCHAR(20)
SET @Match_status = 'Not Created'

SELECT 
    A.customer_id,
    CASE 
        WHEN COUNT(B.customer_id) > 0 THEN 'Created'
        ELSE 'Not Created'
    END AS 'Is an Invoice Created'
FROM customers A
LEFT JOIN orders B ON A.customer_id = B.customer_id
WHERE A.customer_id > 2
GROUP BY A.customer_id
HAVING CASE 
           WHEN COUNT(B.customer_id) > 0 THEN 'Created'
           ELSE 'Not Created'
       END = @Match_status

关键说明： #

• 声明变量@Match_status存储目标状态，后续修改筛选条件只需调整变量值即可
• 用COUNT(B.customer_id)判断客户是否存在订单，避免单个客户多条订单导致的重复记录
• GROUP BY A.customer_id保证每个客户仅返回一条结果
• 使用HAVING子句筛选分组后的状态，因为WHERE无法直接引用CASE生成的别名

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