如何用单条含CTE的SQL查询找出仍在内部的用户card_code

现有两张MySQL表：

• users表：id, name, card_code
• events表：id, user_id, type, scanned_at

其中type字段取值：

• 1：进入扫描（IN）
• 2：离开扫描（OUT）

用户单日可多次进出，满足以下任一条件则判定用户仍在内部：

• 仅有type=1的记录（未离开）
• 最新的type=1记录时间晚于最新的type=2记录时间（最后一次操作是进入）

单条CTE查询实现 #

WITH user_daily_scans AS (
    SELECT
        u.card_code,
        MAX(CASE WHEN e.type = 1 THEN e.scanned_at END) AS latest_in,
        MAX(CASE WHEN e.type = 2 THEN e.scanned_at END) AS latest_out
    FROM events e
    JOIN users u ON e.user_id = u.id
    WHERE e.scanned_at BETWEEN '2025-04-29 00:00:00' AND '2025-04-29 23:59:59'
    GROUP BY u.card_code
)
SELECT card_code
FROM user_daily_scans
WHERE 
    latest_out IS NULL -- 无离开记录，仅进入过
    OR latest_in > latest_out; -- 最后一次操作是进入

逻辑说明 #

1. CTEuser_daily_scans通过聚合操作，为每个用户的card_code计算出当天最新的进入时间（latest_in）和离开时间（latest_out）
2. 主查询筛选出两种符合条件的用户：没有离开记录的，或者最新进入时间晚于最新离开时间的，最终返回对应的card_code

结合示例数据，该查询会返回用户ID5和7的card_code，与预期结果一致。

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