你的位置:首页 > 数据库

[数据库]查找字段连续相同的最大值


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

分组后的数据如下图

第二步,按照groupid字段分组,并找出组中最大的cn值。

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

最终结果如图