如何编写SQL查询判断两人是否在完全相同日期前往同一地点？

嘿，要判断两个用户是否在完全相同的日期访问同一地点，核心是验证他们针对该地点的访问日期集合完全重合——没有任何一方有对方没有的日期。下面是具体的实现方案：

核心逻辑 #

两个用户A和B在地点P的日期集合完全相同，需要同时满足：

• A在P的所有日期，B都有；
• B在P的所有日期，A都有；
• A和B是不同的用户。

我们可以用NOT EXISTS结合EXCEPT来检查是否存在“一方有而另一方没有”的日期，这种方式直观且高效。

完整SQL代码 #

SELECT 
    v1.username AS user1,
    v1.name AS name1,
    v2.username AS user2,
    v2.name AS name2,
    v1.place,
    -- 完全匹配返回1，否则返回0
    CASE 
        WHEN NOT EXISTS (
            -- 检查user1有但user2没有的日期
            SELECT date FROM visitation v3
            WHERE v3.username = v1.username AND v3.place = v1.place
            EXCEPT
            SELECT date FROM visitation v4
            WHERE v4.username = v2.username AND v4.place = v1.place
        ) 
        AND NOT EXISTS (
            -- 检查user2有但user1没有的日期
            SELECT date FROM visitation v4
            WHERE v4.username = v2.username AND v4.place = v1.place
            EXCEPT
            SELECT date FROM visitation v3
            WHERE v3.username = v1.username AND v3.place = v1.place
        )
        THEN 1
        ELSE 0
    END AS is_full_match
FROM visitation v1
JOIN visitation v2 
    ON v1.place = v2.place 
    AND v1.username < v2.username -- 避免重复配对（如john&doe和doe&john只出现一次）
GROUP BY v1.username, v1.name, v2.username, v2.name, v1.place;

代码解释 #

1. 自连接表：通过v1.place = v2.place确保同一地点，v1.username < v2.username避免重复的用户配对（如果需要所有反向配对，可改为v1.username != v2.username）。
2. EXCEPT检查差异：
   • 第一个EXCEPT会找出user1有但user2没有的日期，NOT EXISTS确保不存在这类日期；
   • 第二个EXCEPT反向检查，确保user2没有user1没有的日期。
3. 结果输出：用CASE语句返回1（完全匹配）或0（不匹配）。

示例数据验证 #

针对你给出的示例数据：

• John在Walmart的日期：'15/03/2018'、'10/02/2018'、'03/01/2018'
• Doe在Walmart的日期：'15/03/2018'、'10/02/2018'

第一个EXCEPT会得到'03/01/2018'，所以NOT EXISTS为false，最终is_full_match返回0，符合预期。

备选方案（聚合统计） #

如果你更习惯用聚合函数，也可以通过日期数量对比来实现：

SELECT 
    v1.username AS user1,
    v1.name AS name1,
    v2.username AS user2,
    v2.name AS name2,
    v1.place,
    CASE 
        -- 先判断日期总数是否相同
        WHEN (SELECT COUNT(DISTINCT date) FROM visitation WHERE username = v1.username AND place = v1.place) 
             = (SELECT COUNT(DISTINCT date) FROM visitation WHERE username = v2.username AND place = v1.place)
        -- 再判断共同日期数等于各自的日期数
        AND (SELECT COUNT(DISTINCT v3.date) FROM visitation v3 
             JOIN visitation v4 ON v3.date = v4.date
             WHERE v3.username = v1.username AND v4.username = v2.username AND v3.place = v1.place)
             = (SELECT COUNT(DISTINCT date) FROM visitation WHERE username = v1.username AND place = v1.place)
        THEN 1
        ELSE 0
    END AS is_full_match
FROM visitation v1
JOIN visitation v2 
    ON v1.place = v2.place 
    AND v1.username < v2.username
GROUP BY v1.username, v1.name, v2.username, v2.name, v1.place;

这个方案先检查两个用户的日期总数是否一致，再检查共同日期数等于各自的日期数，从而确保集合完全重合。

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