一个按字段解决断号的处理我现在要实现这样一个业务,表是这样的: 列名 : Min Max 1 5 6 10 11 15 20 30 40 50 51 60 我要得到最终的结果是: 1 15 20 30 40 60 也就是说,只要中间是连续的,就不显示,而只显示起始和终止. 谢谢了,大家帮个忙!! if object_id('pubs..tb') is not null drop table tb gocreate table tb ( min int, max int ) insert into tb(min,max) values(1,5) insert into tb(min,max) values(6,10) insert into tb(min,max) values(11,15) insert into tb(min,max) values(20,30) insert into tb(min,max) values(40,50) insert into tb(min,max) values(51,60) goif object_id('pubs..test') is not null drop table test go select id=identity(int,1,1),* into test from tb delete from tb --你可以自己再创建一个和TB同样的表 godeclare @cnt1 as int --min declare @cnt2 as int --max declare @cnt as int --循环变量 declare @cntM as int --中间值 declare @cntT as int --记录总数 set @cnt = 1 select @cntT = count(*) from test while @cnt <= @cntT begin if @cnt = 1 --第一条记录 begin select @cnt1 = min from test where id = @cnt select @cnt2 = max from test where id = @cnt end else if @cnt = @cntT --最后一条记录 begin select @cnt2 = max from test where id = @cnt insert into tb values(@cnt1 , @cnt2) end else begin select @cntM = min from test where id = @cnt if @cntM <> @cnt2 + 1 begin insert into tb values(@cnt1 , @cnt2) select @cnt1 = min from test where id = @cnt select @cnt2 = max from test where id = @cnt end else begin select @cnt2 = max from test where id = @cnt end end set @cnt = @cnt + 1 end select * from tb drop table tb drop table testmin max ----------- ----------- 1 15 20 30 40 60(所影响的行数为 3 行) create table test ( [min] int, [max] int )insert into test select 1, 5 insert into test select 6, 10 insert into test select 11, 15 insert into test select 20, 30 insert into test select 40, 50 insert into test select 51, 60 --语句 select [min],[max] = (select min([max]) from test t1 where [max]>t2.[min] and not exists(select 1 from test where [min] = t1.[max]+1) ) from test t2 where not exists(select 1 from test where [max] = t2.[min]-1)--结果 1 15 20 30 40 60
set nocount on
declare @t table(a int,b char(6),flag int)
insert @t select 1,'one',NULL
insert @t select 2,'two',NULL
insert @t select 3,'three',NULL
insert @t select 5,'four',NULL
insert @t select 6,'five',NULL
insert @t select 10,'six',NULL
insert @t select 11,'seven',NULLselect *
from @t aa
where (a - (select max(a) from @t where aa.a > a)) > 1
/*a b flag
----------- ------ -----------
5 four NULL
10 six NULL
*/
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx
一个按字段解决断号的处理我现在要实现这样一个业务,表是这样的:
列名 : Min Max
1 5
6 10
11 15
20 30
40 50
51 60
我要得到最终的结果是:
1 15
20 30
40 60
也就是说,只要中间是连续的,就不显示,而只显示起始和终止.
谢谢了,大家帮个忙!!
if object_id('pubs..tb') is not null
drop table tb
gocreate table tb
(
min int,
max int
)
insert into tb(min,max) values(1,5)
insert into tb(min,max) values(6,10)
insert into tb(min,max) values(11,15)
insert into tb(min,max) values(20,30)
insert into tb(min,max) values(40,50)
insert into tb(min,max) values(51,60)
goif object_id('pubs..test') is not null
drop table test
go
select id=identity(int,1,1),* into test from tb
delete from tb --你可以自己再创建一个和TB同样的表
godeclare @cnt1 as int --min
declare @cnt2 as int --max
declare @cnt as int --循环变量
declare @cntM as int --中间值
declare @cntT as int --记录总数
set @cnt = 1
select @cntT = count(*) from test
while @cnt <= @cntT
begin
if @cnt = 1 --第一条记录
begin
select @cnt1 = min from test where id = @cnt
select @cnt2 = max from test where id = @cnt
end
else
if @cnt = @cntT --最后一条记录
begin
select @cnt2 = max from test where id = @cnt
insert into tb values(@cnt1 , @cnt2)
end
else
begin
select @cntM = min from test where id = @cnt
if @cntM <> @cnt2 + 1
begin
insert into tb values(@cnt1 , @cnt2)
select @cnt1 = min from test where id = @cnt
select @cnt2 = max from test where id = @cnt
end
else
begin
select @cnt2 = max from test where id = @cnt
end
end
set @cnt = @cnt + 1
end
select * from tb
drop table tb
drop table testmin max
----------- -----------
1 15
20 30
40 60(所影响的行数为 3 行)
create table test
(
[min] int,
[max] int
)insert into test select 1, 5
insert into test select 6, 10
insert into test select 11, 15
insert into test select 20, 30
insert into test select 40, 50
insert into test select 51, 60
--语句
select [min],[max] = (select min([max]) from test t1
where [max]>t2.[min] and not exists(select 1 from test where [min] = t1.[max]+1)
)
from test t2
where not exists(select 1 from test where [max] = t2.[min]-1)--结果
1 15
20 30
40 60