sql - 仅计算 MS Access 中的连续日期

Question

我正在尝试查询以计算某些客户的连续逾期天数。我还有一个识别客户的主键。

示例表

Date (d.m.y)     Name 
01.01.2014       Alex
02.01.2014       Alex
03.01.2014       Alex
01.01.2014       Bianca
02.01.2014       Bianca
08.07.2014       Alex
09.07.2014       Alex
10.07.2014       Alex
11.07.2014       Alex

如何区分名称并在制作 SELECT COUNT() 时只计算连续的天数？

Desired Result
Name     Overdue Day Count   Date
Alex          3              01.01.2014  <== The date is the first overdue date
Bianca        2              01.01.2014
Alex          4              08.07.2014

Answer 1

这是一个 gaps and islands problem (特别是岛屿)- 不幸的是，我认为唯一支持 Access 的解决方案效率很低:

SELECT  Name, 
        COUNT(*) AS Days,
        MIN(Date) AS FirstDate, 
        MAX(Date) AS LastDate
FROM    (   SELECT  Name,
                    Date,
                    (   SELECT  MIN(B.Date)
                        FROM    T AS B
                        WHERE   B.Date >= A.Date
                        AND     B.Name = A.Name
                        AND     NOT EXISTS
                                (   SELECT  1
                                    FROM    T AS C
                                    WHERE   C.Name = B.Name
                                    AND     C.Date = B.Date + 1
                                )
                    ) AS grp
            FROM    T AS A
        ) AS D
GROUP BY Name, grp;

上面链接的文章中有完整的解释，但是用于创建列 grp 的子查询找到每个特定岛的末端，然后可以使用此值对外部查询。