（邹建请进）求一存储过程，弄了一整天，没有结果，大家帮帮忙

Question

感觉你没描述清楚：输出结果受State中记录的顺序的影响，而这个顺序你并没有规定，是按照IP值从小到大排列后的State么？

Answer 1

create table IP (ip char(20),AreaID int, State int)
insert into IP select '202.200.120.0'  ,    1    ,       0
insert into IP select '202.200.120.4'  ,    1    ,       0
insert into IP select '202.200.120.8 ' ,   1     ,      0
insert into IP select '202.200.120.12' ,   1     ,      1
insert into IP select '202.200.120.16' ,  1      ,    0
insert into IP select '202.200.120.20' , 1       ,   0
insert into IP select '202.200.120.24' ,2        ,  0
insert into IP select '202.200.120.28' ,2         , 0select *,0 as tag  into #t from ip--开始按需求编号
declare  @tag int
set @tag =0
update #t set tag=@tag, @tag=@tag+case when state=1 then 1 else 0 endselect min(ip) Ipbegin ,max(ip) ipend, AreaID from #t where state<>1 group by AreaID,tag drop table #t
drop table Ip/*Ipbegin              ipend                AreaID      
-------------------- -------------------- ----------- 
202.200.120.0        202.200.120.8        1
202.200.120.16       202.200.120.20       1
202.200.120.24       202.200.120.28       2（所影响的行数为 3 行）
*/

Answer 2

CREATE function f_GetPIP(
@ip  varchar(32)
)
returns varchar(32)
as
begin
  declare @is_ip varchar(32),@flag int
  set @is_ip = @ip
  set @flag  = 0
  while @flag = 0
  begin
    declare @ip_p varchar(32),@State int
    select top 1 @ip_p = ip,@State = State from yourtable where left(ip,11) = left(@is_ip,11) and cast(replace(ip,'.','') as int) < cast(replace(@is_ip,'.','') as int) order by ip desc
    if @ip_p is null or @State = 0 set @flag = 1
    else set @is_ip = @ip_p
  end
  return(@is_ip)
endCREATE function f_GetEIP(
@ip  varchar(32)
)
returns varchar(32)
as
begin
  declare @is_ip varchar(32),@flag int
  set @is_ip = @ip
  set @flag  = 0
  while @flag = 0
  begin
    declare @ip_p varchar(32),@State int
    select top 1 @ip_p = ip,@State = State from yourtable where left(ip,11) = left(@is_ip,11) and cast(replace(ip,'.','') as int) > cast(replace(@is_ip,'.','') as int) order by ip Asc
    if @ip_p is null or @State = 0 set @flag = 1
    else set @is_ip = @ip_p
  end
  return(@is_ip)
endselect distinct dbo.f_GetPIP(ip) Ipbegin,dbo.f_GetEIP(ip) Ipend,AreaId from yourtable

Answer 3

CREATE function f_GetPIP(
@ip  varchar(32)
)
returns varchar(32)
as
begin
  declare @is_ip varchar(32),@flag int
  set @is_ip = @ip
  set @flag  = 0
  while @flag = 0
  begin
    declare @ip_p varchar(32),@State int
    select top 1 @ip_p = ip,@State = State from yourtable where left(ip,11) = left(@is_ip,11) and cast(replace(ip,'.','') as int) < cast(replace(@is_ip,'.','') as int) order by ip desc
    if @ip_p is null or @State = 0 set @flag = 1
    else set @is_ip = @ip_p
  end
  return(@is_ip)
endCREATE function f_GetEIP(
@ip  varchar(32)
)
returns varchar(32)
as
begin
  declare @is_ip varchar(32),@flag int
  set @is_ip = @ip
  set @flag  = 0
  while @flag = 0
  begin
    declare @ip_p varchar(32),@State int
    select top 1 @ip_p = ip,@State = State from yourtable where left(ip,11) = left(@is_ip,11) and cast(replace(ip,'.','') as int) > cast(replace(@is_ip,'.','') as int) order by ip Asc
    if @ip_p is null or @State = 0 set @flag = 1
    else set @is_ip = @ip_p
  end
  return(@is_ip)
endselect distinct dbo.f_GetPIP(ip) Ipbegin,dbo.f_GetEIP(ip) Ipend,AreaId from yourtable
where state = 0

Answer 4

可能我没说清楚，其中IP值的长度是不一致的。state只是表示该IP的状态，0表示未用的，1表示已经使用的，现在主要是要按地市统计未用的连续IP段

Answer 5

wyb0026(小小) 的问题只在于是没有考虑IP值的长度是不一致

Answer 6

--测试数据
create table IP (ip char(15),AreaID int,State int)
insert into IP select '202.200.120.0'  ,1,0
insert into IP select '202.200.120.4'  ,1,0
insert into IP select '202.200.120.8 ' ,1,0
insert into IP select '202.200.120.12' ,1,1
insert into IP select '202.200.120.16' ,1,0
insert into IP select '202.200.120.20' ,1,0
insert into IP select '202.200.120.24' ,2,0
insert into IP select '202.200.120.28' ,2,0
GO--创建一个IP格式化的函数
create function dbo.f_formatip(@ip varchar(15))
returns varchar(15)
as
begin
declare @re varchar(16)
set @re=''
select @re=@re+right('00'
+substring(@ip,id,charindex('.',@ip+'.',id)-id)
,3)+'.'
from(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select 2 union all select 3
)a,(
select id=0 union all select 4
union all select 8 union all select 12
)b
)a where substring('.'+@ip,id,1)='.'
return(left(@re,15))
end
go--处理
select *,0 as tag  into #t from ip order by AreaID,dbo.f_formatip(ip)--开始按需求编号
declare  @tag int
set @tag =0
update #t set tag=@tag, @tag=@tag+case when state=1 then 1 else 0 end
select min(ip) Ipbegin ,max(ip) ipend, AreaID from #t where state<>1 group by AreaID,tag 
drop table #tGO
drop table IP
drop function f_formatip/*--结果Ipbegin         ipend           AreaID      
--------------- --------------- ----------- 
202.200.120.0   202.200.120.0   1
202.200.120.16  202.200.120.8   1
202.200.120.24  202.200.120.28  2（所影响的行数为 3 行）
--*/

Answer 7

不好意思我不是做网络编成的，也没用过IP作为值，f_formatip这个函数的目的是作什么的请邹大哥明示

Answer 8

问一下邹大哥，f_formatip函数是用来干什么的

Answer 9

还有就是这并不是我要的结果，结果应该是
Ipbegin         ipend           AreaID      
--------------- --------------- ----------- 
202.200.120.0        202.200.120.8        1
202.200.120.16       202.200.120.20       1
202.200.120.24       202.200.120.28       2
并且Ipbegin 应该小于ipend的

Answer 10

这种情况只能使用游标
先按IP排序，根据state判断
楼主的2表分别设为table1,table2
代码如下：
declare @ip,@Areaid,@state,@beginip,@endip
set @beginip=''
declare me cursor for
select IP,AreaID,State from table1 order by IP
for readonlyopen me
fetch next from me into @ip,@Areaid,@state
while @@fetch_stat=0
begin
     if @beginip='' and @state=0
     begin
       set @beginip=@ip
     end
     if @state=1
     begin
        insert into table2(Ipbegin,Ipend,areaid)values(@beginip,@endip,@Areaid)
     end
     set @endip=@ip
     fetch next from me into @ip,@Areaid,@state
end
insert into table2(Ipbegin,Ipend,areaid)values(@beginip,@endip,@Areaid)GO

Answer 11

原来变化还和你的AreaID有关所以做了相应的变化
create table IP (ip char(20),AreaID int, State int)
insert into IP select '61.243.97.80' ,67 ,0        
insert into IP select '61.243.97.84' ,67 ,0
insert into IP select '61.243.97.88' ,67 ,0
insert into IP select '61.243.97.92' ,68 ,0
insert into IP select '61.243.97.96' ,68 ,0
insert into IP select '61.243.97.100' ,68 ,1
insert into IP select '61.243.97.104' ,68 ,0
insert into IP select '61.243.97.108' ,68 ,0
insert into IP select '61.243.97.112' ,67 ,0
insert into IP select '61.243.97.116' ,67 ,0
insert into IP select '61.243.97.120' ,67 ,0
insert into IP select '61.243.97.124' ,67 ,1
insert into IP select '61.243.97.128' ,67 ,1select *,0 as tag  into #t from ipselect * from #t--开始按需求编号
declare  @tag int
declare  @tag1 int
set @tag =0
set @tag1=0 
update #t set tag=@tag, @tag=@tag+case when state=1 or AreaID<>@tag1 then 1 else 0 end, @tag1=AreaID
select min(ip) Ipbegin ,max(ip) ipend, AreaID from #t where state<>1 group by AreaID,tag drop table #t
drop table Ip--看看行不行
/*
Ipbegin              ipend                AreaID      
-------------------- -------------------- ----------- 
61.243.97.80         61.243.97.88         67
61.243.97.92         61.243.97.96         68
61.243.97.104        61.243.97.108        68
61.243.97.112        61.243.97.120        67（所影响的行数为 4 行）
*/

Answer 12

to：wyb0026(小小)
还是不行
这样的数据可以，但是为什么我一用到数据量大的时候就不行了呢
有这样的分段：
Ipbegin              ipend                AreaID      
-------------------- -------------------- ----------- 
61.243.97.0        61.243.97.24            67
61.243.97.44        61.243.97.88           67
61.243.97.112         61.243.97.116        67
61.243.97.136        61.243.97.252         68
61.243.111.228        61.243.111.240       68
其中61.243.97.44        61.243.97.88 这一段就不显示，直接就是 61.243.97.0 -61.243.97.116 
怎么回事嘛，都快崩快了

Answer 13

其实到现在我也不算明白你问题的意思
关键在于分段的条件
我认为
State 遇见1 时要分段 或者AreaID 只要值发生变化就分段所以关键的一句话我写成这样
@tag=@tag+case when state=1 or AreaID<>@tag1 then 1 else 0 endwhen state=1 or AreaID<>@tag1  //条件
这只是我的理解，不知对不对
我不建议用游标效率不高

Answer 14

to：wyb0026(小小) 
你的理解没错，就是这样的，但是最后执行
select min(ip) Ipbegin ,max(ip) ipend, AreaID from #t where state<>1 group by AreaID,tag 
的时候出来的结果就不是我想要的了

Answer 15

wyb0026(小小)的方法是对的，但是IP是字符串，比较的时候是按位比较的，所以因该先把用zjcxc(邹建) 的函数f_formatip把IP转化为长度一致的，在进行比较，应该就没有问题了

Answer 16

我的函数只是把ip地址转换成标准的格式: xxx.xxx.xxx.xxxto：wyb0026(小小)
你的错误在于不转换的时候,由于ip地址不是标准的格式,按你的比较:
5.1.1.1 就大于 205.1.1.1 
这显然不对吧?我当时也没细想,犯了同样的错误,因为最终出结果是通过max与min的,所以应该还要改一下:select *,0 as tag,id=identity(int)  into #t from ip order by AreaID,dbo.f_formatip(ip)
--开始按需求编号
declare  @tag int
declare  @tag1 int
set @tag =0
set @tag1=0 
update #t set tag=@tag, @tag=@tag+case when state=1 or AreaID<>@tag1 then 1 else 0 end, @tag1=AreaID
--select min(ip) Ipbegin ,max(ip) ipend, AreaID from #t where state<>1 group by AreaID,tag 
select Ipbegin=a.ip,ipend=b.ip,a.AreaID
from #t a,#t b,(
select id1=min(id),id2=max(id) from #t where state<>1 group by AreaID,tag )c
where a.id=c.id1 and b.id=c.id2drop table #t