SQL Server 查詢每日每用戶首末刷卡記錄的高效寫法

業務需求

刷卡表包含：ID、用戶ID、刷卡時間等欄位，與用戶表為多對一關係。需求：查詢「昨天」每位用戶的最早刷卡時間與最晚刷卡時間。

核心 SQL 解法

SELECT 
    b.username,
    MAX(a.刷卡時間) AS 最大刷卡時間,
    MIN(a.刷卡時間) AS 最小刷卡時間
FROM 刷卡表 a
INNER JOIN 用戶表 b ON a.用戶ID = b.用戶ID
WHERE a.刷卡日期 = DATEADD(day, -1, CAST(GETDATE() AS DATE))
GROUP BY b.username;

關鍵點解析

1. 日期篩選優化

-- 推薦：只比較日期部分，利用索引
WHERE a.刷卡日期 = DATEADD(day, -1, CAST(GETDATE() AS DATE))

-- 不推薦：函數包裹欄位導致索引失效
WHERE DATEADD(day, -1, a.刷卡時間) = CAST(GETDATE() AS DATE)

2. 聚合函數運用

• MAX(刷卡時間)：獲取該用戶當日最晚記錄
• MIN(刷卡時間)：獲取該用戶當日最早記錄

3. GROUP BY 分組邏輯

按 username 分組，確保每用戶輸出一行結果。

進階需求：同時獲取完整刷卡記錄行

若需要首/末刷卡的完整資訊（如刷卡地點、設備ID）：

WITH Ranked AS (
    SELECT 
        a.*,
        b.username,
        ROW_NUMBER() OVER (PARTITION BY a.用戶ID ORDER BY a.刷卡時間 ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY a.用戶ID ORDER BY a.刷卡時間 DESC) AS rn_desc
    FROM 刷卡表 a
    INNER JOIN 用戶表 b ON a.用戶ID = b.用戶ID
    WHERE a.刷卡日期 = DATEADD(day, -1, CAST(GETDATE() AS DATE))
)
SELECT * FROM Ranked WHERE rn_asc = 1 OR rn_desc = 1;

索引建議

-- 覆蓋索引，支援日期篩選 + 分組 + 聚合
CREATE INDEX IX_刷卡表_日期_用戶_時間 
ON 刷卡表(刷卡日期, 用戶ID) INCLUDE (刷卡時間);

效能對比

總結

使用 GROUP BY + MAX/MIN 是最簡潔高效的解法。關鍵在於日期欄位設計（建議冗余日期欄位）與適當索引，避免函數運算導致索引失效。