如何编写SQL查询提取仅特定日期组合的客户数据？

首先假设你的表结构为customer_visits，包含customer_id（客户唯一标识）和visit_day（到访日期，可存储为字符串如'Monday'或数字1-5对应周一至周五）。以下是两种高效的实现方式：

方法1：使用NOT EXISTS子查询 #

通过检查客户是否不存在周二/周四的到访记录，筛选出符合要求的客户：

-- 若visit_day为字符串格式（如'Monday'）
SELECT DISTINCT customer_id
FROM customer_visits cv
WHERE NOT EXISTS (
    SELECT 1
    FROM customer_visits cv2
    WHERE cv2.customer_id = cv.customer_id
      AND cv2.visit_day IN ('Tuesday', 'Thursday')
);

-- 若visit_day为数字格式（1=周一，2=周二，3=周三，4=周四，5=周五）
SELECT DISTINCT customer_id
FROM customer_visits cv
WHERE NOT EXISTS (
    SELECT 1
    FROM customer_visits cv2
    WHERE cv2.customer_id = cv.customer_id
      AND cv2.visit_day IN (2, 4)
);

方法2：使用GROUP BY + HAVING过滤 #

按客户分组后，统计周二/周四的到访记录数，仅保留统计数为0的客户：

-- 字符串格式visit_day
SELECT customer_id
FROM customer_visits
GROUP BY customer_id
HAVING SUM(CASE WHEN visit_day IN ('Tuesday', 'Thursday') THEN 1 ELSE 0 END) = 0;

-- 数字格式visit_day
SELECT customer_id
FROM customer_visits
GROUP BY customer_id
HAVING SUM(CASE WHEN visit_day IN (2, 4) THEN 1 ELSE 0 END) = 0;

扩展：输出客户到访日期组合 #

如果需要同时展示客户对应的到访日期组合（匹配示例图样式），可使用字符串聚合函数：

-- MySQL 示例
SELECT 
    customer_id,
    GROUP_CONCAT(DISTINCT visit_day ORDER BY visit_day SEPARATOR ' - ') AS visit_day_combination
FROM customer_visits
GROUP BY customer_id
HAVING SUM(CASE WHEN visit_day IN ('Tuesday', 'Thursday') THEN 1 ELSE 0 END) = 0;

-- PostgreSQL 示例
SELECT 
    customer_id,
    STRING_AGG(DISTINCT visit_day, ' - ' ORDER BY visit_day) AS visit_day_combination
FROM customer_visits
GROUP BY customer_id
HAVING SUM(CASE WHEN visit_day IN ('Tuesday', 'Thursday') THEN 1 ELSE 0 END) = 0;

逻辑说明 #

两种方法核心思路一致：确保客户的所有到访日期仅包含周一、周三、周五，排除任何涉及周二/周四的记录。扩展写法通过聚合函数将客户的到访日期拼接成组合字符串，贴合示例图的输出样式。

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