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

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

--添加一列自增数量，并插入到新表#t
SELECT 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
             )

SELECT  id ,
            cn
    FROM    ( SELECT    id ,
                        cn ,
                        MAX(cn) OVER ( PARTITION BY groupid ) AS mcn
              FROM      t0
            ) t
    WHERE   cn = mcn