我有一个表
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