数据库环境：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
最终结果如图
- 1楼神崎橙
- Po主你好，我这里也有一句sql 也能实现这个效果，，;WITH CTE AS (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 1)) AS RowFROM #tt)SELECT ID,MAX(cn) AS cnFROM CTEGROUP BY id,CTE.Row，不用用到临时表哦
- Re: 利利家的猪
- @神崎橙，思路清晰，实现方法简单。佩服