如何在 SQL Server 2000 中返回基于连续时间分配的记录列表

Question

我有一个如下所示的表：

IP            Hostname   TransactionDate
------------- ---------- -------------------
1.1.1.1       A          2009-01-01 01:00:00
1.1.1.1       A          2009-01-02 01:00:00
1.1.1.1       A          2009-01-03 01:45:00
1.1.1.1       B          2009-01-04 01:00:00
1.1.1.1       A          2009-01-05 01:00:00

我想构建一个查询来返回记录，该记录将根据连续持有 IP 地址的时间对结果进行分组：

即（选择或 IP 1.1.1.1）：

Hostname    GrantDate            ExpireDate
----------- ---------------      -----------------
A           2009-01-01 01:00:00  2009-01-04 01:00:00
B           2009-01-04 01:00:00  2009-01-05 01:00:00
A           2009-01-05 01:00:00  NULL

最好的方法是什么完成这个任务？ 我想尽可能避免使用光标。 我正在使用 SQL Server 2000，这使得这变得更加困难......

Answer 1

Joe Celko 的 SQL 谜题和答案中提供了一些针对此问题的解决方案。 Google 有 摘录，其中包括它们。 他的示例包括开始时间和结束时间，但调整它们应该相当简单。

Answer 2

为了后代的缘故而发布。 我只是找不到一种不使用光标的方法来做到这一点。 我确信，部分原因是 SQL 不是我的强项。 如果其他人看到这篇文章，这是我使用的解决方案。 我将继续努力使用查询来查询它，如果我弄清楚了，我会发布它。

SET NOCOUNT ON
DECLARE @ip VARCHAR(15)
    SET @ip = '1.1.1.1'

DECLARE @dhcplog TABLE(IP VARCHAR(15), HOSTNAME VARCHAR(32), IPDATE DATETIME)
DECLARE @results TABLE(IP VARCHAR(15), HOSTNAME VARCHAR(32), STARTDATE DATETIME, ENDDATE DATETIME)
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-01 01:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-02 02:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-05 03:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-07 04:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-07 10:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-08 05:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'C', '2009-01-09 06:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-10 07:00:00')

DECLARE @cHOST VARCHAR(32)
DECLARE @cEND DATETIME

DECLARE @tIP VARCHAR(15)
DECLARE @tHOST VARCHAR(32)
DECLARE @tIPDATE DATETIME

DECLARE IPCursor CURSOR FOR SELECT IP, HOSTNAME, IPDATE
                            FROM @dhcplog
                            WHERE IP = @ip
                            GROUP BY IP, HOSTNAME, IPDATE
                            ORDER BY IPDATE DESC

OPEN IPCursor
    FETCH NEXT FROM IPCursor INTO @tIP, @tHOST, @tIPDATE
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @tHOST = @cHOST
                    BEGIN
                        UPDATE @results
                        SET STARTDATE = @tIPDATE
                        WHERE HOSTNAME = @cHOST
                        AND ENDDATE = @cEND
                    END
                ELSE
                    BEGIN
                        INSERT INTO @results (IP, HOSTNAME, STARTDATE, ENDDATE)
                        VALUES (@tIP, @tHOST, @tIPDATE, @tIPDATE)
                        SET @cHOST = @tHOST
                        SET @cEND = @tIPDATE
                    END
                FETCH NEXT FROM IPCursor INTO @tIP, @tHOST, @tIPDATE
            END
CLOSE IPCursor
DEALLOCATE IPCursor

SELECT * FROM @results

SET NOCOUNT OFF

结果：

IP      Hostname    StartDate       EndDate
1.1.1.1 A       2009-01-10 07:00:00 2009-01-10 07:00:00
1.1.1.1 C       2009-01-09 06:00:00 2009-01-09 06:00:00
1.1.1.1 B       2009-01-07 04:00:00 2009-01-08 05:00:00
1.1.1.1 A       2009-01-01 01:00:00 2009-01-05 03:00:00