如何求出连续范围段

一列数字的分区最大值与最小值收藏
我有一个表
num
001
002
003
004
007
008
009   我想让他们变成区间形式
a    b
001  004
007  009
如何才能办到，我需要的是一条sql语句declare @t table(num varchar(10))
insert into @t select '001'
insert into @t select '002'
insert into @t select '003'
insert into @t select '004'
insert into @t select '007'
insert into @t select '008'
insert into @t select '009' --法一:钻钻libin_ftsafe
select
    a.num as [a],min(b.num) as [b]
from
    (select * from @t t where not exists(select 1 from @t where num=t.num-1)) a,
    (select * from @t t where not exists(select 1 from @t where num=t.num+1)) b
where
    a.num<=b.num
group by a.num /*
a          b
---------- ----------
001        004
007        009
*/--法二:.net小鸟declare @T table(num varchar(10))
insert into @T
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '007' union all
select '008' union all
select '009'
declare @T table(num varchar(10))
insert into @T
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '007' union all
select '008' union all
select '009' select min(num) as a,max(num) as b from
(
    select px = row_number() over(order by num),*
    from @T
)T
group by cast(num as int)-px
/*
001    004
007    009
*/--法三:每天进步一点点
DECLARE @TB TABLE([num] VARCHAR(3))
INSERT @TB
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '003' UNION ALL
SELECT '004' UNION ALL
SELECT '007' UNION ALL
SELECT '008' UNION ALL
SELECT '009';WITH CTE AS
(
SELECT *,ID=ROW_NUMBER() OVER (ORDER BY num) FROM @TB
)
,CTE2 AS
(SELECT TOP 1 *,GRP=ID FROM CTE WHERE ID=1
UNION ALL
SELECT B.*,CASE WHEN B.num=C.num+1 THEN GRP ELSE GRP+1 END FROM CTE AS B,CTE2 AS C WHERE B.ID=C.ID+1
)
SELECT MIN(num) AS a,
    MAX(num) AS b
FROM CTE2
GROUP BY GRP
/*
a    b
---- ----
001  004
007  009
*/--法四:if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([num] varchar(10))
insert [tb] select '001'
union all select '002'
union all select '003'
union all select '004'
union all select '007'
union all select '008'
union all select '009'
go--增加辅助字段
alter table tb add fid int
go
--更新字段值
declare @i int,@j int
update tb set fid=@i,@i=case when @j=isnull(cast(num as int),0)-1 then isnull(@i,0) else isnull(@i,0)+1 end,@j=isnull(cast(num as int),0)
go
--查询
select a=min(num),b=max(num) from tb group by fid
--结果
/*
a          b
---------- ----------
001        004
007        009(2 行受影响)
*/
--删除辅助字段
alter table tb drop column fid
go
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/06/03/4236684.aspx
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2
-------------- -------------- -----------
a          1           1
a          4           5
b          2           4
--*/
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2
-------------- -------------- -----------
a          1           1
a          4           5
b          2           4
--*/

让你望见影子<[email protected]>  9:49:01
--生成已用编号分布字符串的函数
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000),@pid int
SELECT @re='',@pid=-1
SELECT @re=CASE
WHEN col2=@pid+1 THEN @re
ELSE @re
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END
+','+CAST(col2 as varchar)
END,
@pid=col2
FROM tb
WHERE col1=@col1
ORDER BY col2
RETURN(STUFF(@re,1,2,'')
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END)
END
GO--生成缺号分布字符串的函数
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @re varchar(8000),@pid int
SELECT @re='',@pid=0
SELECT @re=CASE
WHEN col2=@pid+1 THEN @re
ELSE @re+','+CAST(@pid+1 as varchar)
+CASE
WHEN @pid+1=col2-1 THEN ''
ELSE CAST(1-col2 as varchar)
END
END,
@pid=col2
FROM tb
WHERE col1=@col1
ORDER BY col2
RETURN(STUFF(@re,1,1,''))
END
GO--调用测试
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7--查询
SELECT col1,
col2_Series=dbo.f_GetStrSeries(col1),
col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--结果
col1       col2_Series       col2_Series
-------------- ------------------------ --------------
a          2-3,5,8-9        1,4,6-7
b          1,5-7           2-4
--*/

调试易

如何求出连续范围段

解决方案 »