查找字段连续相同的最大值

数据库环境：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             )

分组后的数据如下图