使用SQL查询连续号码段

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

原文http://www.cnblogs.com/tc310/archive/2010/09/17/1829276.html

CREATE TABLE #test(fphm INT ,kshm CHAR(8))

INSERT #test
SELECT 2014,'00000001' UNION ALL
SELECT 2014,'00000002' UNION ALL
SELECT 2014,'00000003' UNION ALL
SELECT 2014,'00000004' UNION ALL
SELECT 2014,'00000005' UNION ALL
SELECT 2014,'00000007' UNION ALL
SELECT 2014,'00000008' UNION ALL
SELECT 2014,'00000009' UNION ALL
SELECT 2013,'00000120' UNION ALL
SELECT 2013,'00000121' UNION ALL
SELECT 2013,'00000122' UNION ALL
SELECT 2013,'00000124' UNION ALL
SELECT 2013,'00000125'

SELECT * FROM #test

SELECT fphm, kshm = MIN(kshm) + ',' + MAX(kshm)
FROM ( SELECT *, gid = kshm - pid
    FROM  ( SELECT ROW_NUMBER() OVER ( PARTITION BY fphm ORDER BY kshm ) pid,
        *
       FROM  #test
     ) s1
  ) s2
GROUP BY fphm, gid