數據庫環境：SQL SERVER 2008R2

有基礎數據如圖1，要求取出id字段連續值為一組的cn最大值，即圖2中紅框圈中的部分。

先導入基礎數據

WITH    x0          AS ( SELECT   1 AS id ,                        100 AS cn               UNION ALL               SELECT   1 AS id ,                        200 AS cn               UNION ALL               SELECT   1 AS id ,                        300 AS cn               UNION ALL               SELECT   2 AS id ,                        400 AS cn               UNION ALL               SELECT   2 AS id ,                        200 AS cn               UNION ALL               SELECT   1 AS id ,                        600 AS cn               UNION ALL               SELECT   1 AS id ,                        700 AS cn             )                 SELECT * INTO #tt FROM x0

實現的步驟分兩步，第一步是將連續id分組，則提供的基礎數據可以分成3組。

--添加一列自增數量，并插入到新表#tSELECT IDENTITY(int,1,1) AS rowid,* INTO #t FROM #tt--將id連續數據分組WITH    t0 ( rowid, id, cn, groupid )          AS ( SELECT   rowid ,                        id ,                        cn ,                        1 AS groupid               FROM     #t               WHERE    rowid = 1               UNION ALL               SELECT   a.rowid ,                        a.id ,                        a.cn ,                        CASE WHEN a.id = b.id THEN b.groupid                             ELSE b.groupid + 1                        END groupid               FROM     #t a                        INNER JOIN t0 b ON b.rowid = a.rowid - 1             )

分組后的數據如下圖