查找字段连续相同的最大值 数据库环境：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 )
分组后的数据如下图
QQrn8/n5z7/An+fzWXvi3f4bEEoyWk+EEICeCCG4Bkn4BhBKMpQnMBBKMpQnMBBKMlLyxN+/hz/+QuaJj0u4fOwI8qX2Py7d1FF3bH5kP94h72E7T/z487emM3w9sZ4nHtfTAe/G+hswa/9xPZ2uj+Gr+ZEjeEFSdpQnxu3PXy7/ci/fN09k8kTsfiKfJ4apoHk9P7JDyDuS8qM8UcsTE5QnanligvJErfuJCcoT4/bxzx0zSdlRnpi2r/kJzWNiIJRkaL0DA6EkQ3kCA6EkQ3kCA6EkI3x+/VSeyA+hJKMvelPhnWhoxw6sMb0FwAOcQyjJkCcwEEoy5AkMhJIMeQIDoSQj4gmPad05sQ5ynNtOmcn+n85tL/7ucT0N1pAdNa53kG/t3UKA+DUwmtq76HlH9scMYJ4w2gC1Vp7sCVSeaMjgiTZA1dTUiZ54XE/BL0sQeMIClCfqFE94G6JGe2IYoDxRRz2RwRA11BPTAHU/EfHExyXP3S/ME/MA8c8dpozSE/1zeQghOOYLlCcWA9T8hOYxMRBKMuQJDISSDHkCA6EkQ57AQCjJaGvvsCLkCSpU9yamaOzAQCjJkCcwEEoy5AkMhJIMeQIDoSQjtfbOdaI11kE5au/sAPRbjq2BtYscvhLXO8h537t5gPg1sG9Te+daeoevqekD1Fp5qid8S+8ipDEv0VVNzcYZTS77Ve8n6nGA8kStGt0O1ej2qJa/RbX8Bvlnfhq8PEH5mZ8GVk/Ug0dz10JdhvkJC1DzE5rHxEAoyZAnMBBKMuQJDISSDHkCA6EkQ7V3GAglGaq9E1M0dmAglGTIExgIJRnyBAZCSYY8gYFQkpHkib4cyYcNAYfMK78UIHxuO89egxskeOJxPYHWO45ahHshQPQaWLZ1xw2innhcT6fLxbPy7qW17OPbHwfIslbeKXPfI2iBmCc+LuHy4S0uoYPc8sQ4QJqampo2T7R/GHBP7NxWKz1AHk/k2UlskUhNzbynPNjuoP29kx4giSeAhqg3PWEfNEDuZ3VI77wQIMH9BNYQdfqeqZg8cdDGey8EiH7uyLbX4AbUnjhq472XAsTOT2Tba3ADzWNiIJRkyBMYCCUZ8gQGQkmGPIGBUJKh2jsMhJIM1d6JKRo7MBBKMuQJDISSDHkCA6EkQ57AQCjJiK6V55hm3egg79o7wu+cJa+9y/SJ9zUB3rV3hPtP0Nfe5Sr+SkmkbrV34/YJ1sqnkrI