原帖:http://blog.csdn.net/htl258/archive/2009/06/02/4236684.aspx
我有一个表
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