SQL Server 刷卡記錄篩選：同表自關聯找間隔≥40分鐘

需求

表 logs(logtime, logcard, logname, logbm)，同一卡號在 2014-12-28 11:00-13:00 內有兩條記錄，時間差 ≥ 40 分鐘，查出該員工數據。

SQL 方案

SELECT DISTINCT a.*
FROM logs a
JOIN logs b
  ON a.logcard = b.logcard
  AND a.logtime < b.logtime
  AND DATEDIFF(MINUTE, a.logtime, b.logtime) >= 40
WHERE a.logtime BETWEEN '2014-12-28 11:00:00' AND '2014-12-28 13:00:00'
  AND b.logtime BETWEEN '2014-12-28 11:00:00' AND '2014-12-28 13:00:00';

優化要點

• 建立索引：CREATE INDEX IX_logs_card_time ON logs(logcard, logtime);
• 使用 DATEDIFF 而非手動換算天數，可用性強、可 SARGable。
• DISTINCT 去重：同一卡可能有多組符合條件的配對。

窗口函數寫法

WITH cte AS (
  SELECT *,
         LEAD(logtime) OVER (PARTITION BY logcard ORDER BY logtime) AS next_time
  FROM logs
  WHERE logtime BETWEEN '2014-12-28 11:00:00' AND '2014-12-28 13:00:00'
)
SELECT * FROM cte
WHERE DATEDIFF(MINUTE, logtime, next_time) >= 40;

SQL Server 2012+ 支持，邏輯更清晰，性能通常優於自連接。