借助分组因子,找出连续值的小例子

如下表

DECLARE @T TABLE (pjbm INT,pjhm VARCHAR(8))
INSERT INTO @T
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 pjbm,min(pjhm) as begin_range,max(pjhm) as end_range from
(
  select pjbm,pjhm,
  grp=(
      select min(pjhm) from @T as b where pjbm=pjbm and pjhm>=a.pjhm
       and not exists(select * from @T where pjhm=b.pjhm+1)
   ) from @T as a
 ) t group by grp,pjbm

 

感觉很不错.像公式一样来用.

当然用sql2005的话.变得更多简单了,因为它构造分组因子变得更简单了,以下是sql2005下的例子

 --select pjbm,pjhm-Row_Number() over(order by pjhm) as grp from @T
select pjbm,min(pjhm) as begin_range,max(pjhm) as end_range from
(
   select pjbm,pjhm,pjhm-Row_Number() over(order by pjhm) as grp from @T
 ) t group by grp,pjbm order by pjbm