如何用T-SQL查询出勤所有指定日期的用户ID

问题背景 #

现有WorkRecords表结构及示例数据如下：

需查询出勤了所有指定日期的UserID（比如示例中需找出同时出勤2023/09/01、09/02、09/03的用户，结果为111111），当前采用CTE加多个UNION的方式性能极差，需要更高效的实现方案。

最优实现方式 #

核心思路：通过分组统计用户匹配指定日期的唯一记录数，与指定日期的总数做对比，匹配的即为全勤用户。

通用SQL写法（支持动态指定日期） #

-- 定义需要检查的指定日期集合
WITH RequiredDates AS (
    SELECT '2023/09/01' AS DateVal UNION ALL
    SELECT '2023/09/02' UNION ALL
    SELECT '2023/09/03'
)
SELECT wr.UserID
FROM WorkRecords wr
JOIN RequiredDates rd ON wr.DateWorked = rd.DateVal
GROUP BY wr.UserID
-- 统计用户匹配到的指定日期数量，等于需要的日期总数则为全勤
HAVING COUNT(DISTINCT wr.DateWorked) = (SELECT COUNT(*) FROM RequiredDates);

简化写法（固定日期场景） #

如果指定日期是固定值，可直接硬编码日期总数，省略CTE：

SELECT UserID
FROM WorkRecords
WHERE DateWorked IN ('2023/09/01', '2023/09/02', '2023/09/03')
GROUP BY UserID
HAVING COUNT(DISTINCT DateWorked) = 3;

性能优化关键点 #

1. 添加复合索引：在WorkRecords表上创建(DateWorked, UserID)的复合索引，让数据库快速筛选指定日期的记录，同时避免分组时的全表扫描：

   CREATE INDEX idx_workrecords_date_user ON WorkRecords(DateWorked, UserID);
   

2. 移除DISTINCT（可选）：如果(UserID, DateWorked)是唯一约束（同一用户同一天无重复记录），可将COUNT(DISTINCT DateWorked)改为COUNT(*)，进一步提升统计效率。

方案优势对比 #

原CTE+UNION方案会生成大量中间结果，且多次扫描表；本方案仅扫描一次指定日期范围内的记录，通过分组统计直接过滤，逻辑简洁，数据库能更好地利用索引优化执行计划，性能提升显著。

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