MySQL同表查询仅第2周预约及第1、2周预约日期不同的学生记录

方案1：基于日期拼接比对（兼容绝大多数SQL dialect） #

核心逻辑是先分别汇总每个学生两周的预约日期，排序后拼接成字符串进行比对，不一致即符合要求：

SELECT DISTINCT w1.childid
FROM (
    -- 汇总每个学生第1周的所有预约日期，去重后按顺序拼接
    SELECT 
        childid,
        GROUP_CONCAT(DISTINCT `Day` ORDER BY `Day`) AS week1_days
    FROM booking
    WHERE `Week` = 1
    GROUP BY childid
) w1
INNER JOIN (
    -- 汇总每个学生第2周的所有预约日期，去重后按顺序拼接
    SELECT 
        childid,
        GROUP_CONCAT(DISTINCT `Day` ORDER BY `Day`) AS week2_days
    FROM booking
    WHERE `Week` = 2
    GROUP BY childid
) w2 ON w1.childid = w2.childid
-- 筛选两周预约日期不一致的学生
WHERE w1.week1_days <> w2.week2_days

用你给出的示例数据测试，该查询会返回ChildId = 3的记录，符合预期。

方案2：基于集合差集比对（支持EXCEPT/MINUS的数据库可用，如PostgreSQL、SQL Server等） #

直接判断两周的预约日期集合是否存在差异：

SELECT DISTINCT b.childid
FROM booking b
WHERE b.`Week` = 2
-- 存在第1周有但第2周没有的日期，或第2周有但第1周没有的日期
AND (
    EXISTS (
        SELECT `Day` FROM booking WHERE childid = b.childid AND `Week` = 1
        EXCEPT
        SELECT `Day` FROM booking WHERE childid = b.childid AND `Week` = 2
    )
    OR EXISTS (
        SELECT `Day` FROM booking WHERE childid = b.childid AND `Week` = 2
        EXCEPT
        SELECT `Day` FROM booking WHERE childid = b.childid AND `Week` = 1
    )
)

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