MS Access查询：按权限状态将日期分组为起止日期范围

原权限表结构及数据 #

查询需求 #

编写SELECT查询，将同一UserID下Permission状态相同的连续日期分组为Date From（起始日期）和Date Till（结束日期）范围，期望结果如下：

补充说明：上述是UserID为1370和2456的部分数据，用户提交权限申请后由系统进行Approved（审批通过）或Rejected（拒绝）操作，每日权限状态可能变动；为避免单独列出全年365天的数据，希望将连续同状态的日期合并为起止日期范围。

解决方案SQL #

假设表名为Permissions，可以使用窗口函数实现连续相同状态的分组：

WITH RankedPermissions AS (
    SELECT
        UserID,
        Date,
        Permission,
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Date) 
        - ROW_NUMBER() OVER (PARTITION BY UserID, Permission ORDER BY Date) AS GroupID
    FROM Permissions
)
SELECT
    UserID,
    MIN(Date) AS [Date From],
    MAX(Date) AS [Date Till],
    Permission
FROM RankedPermissions
GROUP BY UserID, Permission, GroupID
ORDER BY UserID, [Date From];

原理说明 #

• 第一个ROW_NUMBER()按用户ID和日期排序，生成全局行号；
• 第二个ROW_NUMBER()按用户ID、权限状态和日期排序，生成同状态下的行号；
• 两者的差值GroupID会将连续相同权限状态的记录归为同一组；
• 最后按用户ID、权限和GroupID分组，取每组的最小和最大日期作为起止范围。

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