有难度,想了半天没想出,望有高手进来帮楼主解答。(建出表,方便COPY) create table test ( aa varchar(10), bb varchar(10) )insert into test select '11','x' union all select '12','y' union all select '13','z' union all select '14','z' union all select '15','y'
create table test ( aa varchar(10), bb varchar(10) )create table result ( aa varchar(10), bb varchar(10) ) insert into test select '11','x' union all select '12','y' union all select '13','z' union all select '14','z' union all select '15','y'declare @former_aa varchar(10),@former_bb varchar(10)--存放前一条记录 declare @present_aa varchar(10),@present_bb varchar(10)--存放当前记录 declare mycursor cursor--游标 for select * from test set @former_aa='' set @former_bb='' open mycursor fetch next from mycursor into @present_aa,@present_bb while(@@fetch_status=0) begin if(@present_bb=@former_bb) update result set aa=aa+'-'+@present_aa where bb=@present_bb--连续,则更新 else insert into result values(@present_aa,@present_bb)--否则插入 set @former_aa=@present_aa set @former_bb=@present_bb--滚动记录,下次作为前一条记录
fetch next from mycursor into @present_aa,@present_bb end close mycursor deallocate mycursor -----测试结果------ select * from result /* aa bb ---------- ---------- 11 x 12 y 13-14 z 15 y(所影响的行数为 4 行) */
select aa+isnull( '-'+(select max(aa) from 表 b where bb=a.bb and exists(select 1 from 表 where bb=b.bb and aa=cast(b.aa as int)-1)) ,'') ,bb from 表 a where not exists(select 1 from 表 where bb=a.bb and aa=cast(a.aa as int)-1)
--测试数据 declare @t table(aa varchar(10),bb varchar(10)) insert into @t select '11','x' union all select '12','y' union all select '13','z' union all select '14','z' union all select '15','y'--查询 select aa=aa+isnull( '-'+(select max(aa) from @t b where bb=a.bb and exists(select 1 from @t where bb=b.bb and aa=cast(b.aa as int)-1)) ,'') ,bb from @t a where not exists(select 1 from @t where bb=a.bb and aa=cast(a.aa as int)-1)/*--测试结果aa bb --------------------- ---------- 11 x 12 y 13-14 z 15 y(所影响的行数为 4 行) --*/
--加上字段名 select aa=aa+isnull( '-'+(select max(aa) from 表 b where bb=a.bb and exists(select 1 from 表 where bb=b.bb and aa=cast(b.aa as int)-1)) ,'') ,bb from 表 a where not exists(select 1 from 表 where bb=a.bb and aa=cast(a.aa as int)-1)
create table test
(
aa varchar(10),
bb varchar(10)
)insert into test
select '11','x' union all
select '12','y' union all
select '13','z' union all
select '14','z' union all
select '15','y'
(
aa varchar(10),
bb varchar(10)
)create table result
(
aa varchar(10),
bb varchar(10)
)
insert into test
select '11','x' union all
select '12','y' union all
select '13','z' union all
select '14','z' union all
select '15','y'declare @former_aa varchar(10),@former_bb varchar(10)--存放前一条记录
declare @present_aa varchar(10),@present_bb varchar(10)--存放当前记录
declare mycursor cursor--游标
for
select * from test
set @former_aa=''
set @former_bb=''
open mycursor
fetch next from mycursor into @present_aa,@present_bb
while(@@fetch_status=0)
begin
if(@present_bb=@former_bb)
update result set aa=aa+'-'+@present_aa where bb=@present_bb--连续,则更新
else
insert into result values(@present_aa,@present_bb)--否则插入
set @former_aa=@present_aa
set @former_bb=@present_bb--滚动记录,下次作为前一条记录
fetch next from mycursor into @present_aa,@present_bb
end
close mycursor
deallocate mycursor -----测试结果------
select * from result
/*
aa bb
---------- ----------
11 x
12 y
13-14 z
15 y(所影响的行数为 4 行)
*/
http://expert.csdn.net/Expert/topic/2625/2625697.xml?temp=.1925012
'-'+(select max(aa) from 表 b
where bb=a.bb and
exists(select 1 from 表 where bb=b.bb and aa=cast(b.aa as int)-1))
,'')
,bb
from 表 a where not exists(select 1 from 表 where bb=a.bb and aa=cast(a.aa as int)-1)
declare @t table(aa varchar(10),bb varchar(10))
insert into @t
select '11','x'
union all select '12','y'
union all select '13','z'
union all select '14','z'
union all select '15','y'--查询
select aa=aa+isnull(
'-'+(select max(aa) from @t b
where bb=a.bb and
exists(select 1 from @t where bb=b.bb and aa=cast(b.aa as int)-1))
,'')
,bb
from @t a where not exists(select 1 from @t where bb=a.bb and aa=cast(a.aa as int)-1)/*--测试结果aa bb
--------------------- ----------
11 x
12 y
13-14 z
15 y(所影响的行数为 4 行)
--*/
select aa=aa+isnull(
'-'+(select max(aa) from 表 b
where bb=a.bb and
exists(select 1 from 表 where bb=b.bb and aa=cast(b.aa as int)-1))
,'')
,bb
from 表 a where not exists(select 1 from 表 where bb=a.bb and aa=cast(a.aa as int)-1)
http://expert.csdn.net/Expert/TopicView1.asp?id=2676124