高分请求一高难SQL语句---IP整合

Question

韩国 这条记录的 IntMinIP 应该是 3717201920 巴

Answer 1

select x.IntMinIP ,y.IntMaxIP ,x.CharMaxIP,x.AreaName 
from 
(
select AreaName,IntMinIP,CharMinIP from t1 
join 
(
select min(IntMinIP) as MiIntIP ,max(IntMaxIP) as MxIntIP ,AreaName
from t1 
group by AreaName
) a on t1.AreaName =a.AreaName and t1.IntMinIP= a.MiIntIP 
) x 
join 
(
select AreaName,IntMaxIP,CharMaxIP from t1 
join 
(
select min(IntMinIP) as MiIntIP ,max(IntMaxIP) as MxIntIP ,AreaName
from t1 
group by AreaName
) a on t1.AreaName =a.AreaName and t1.IntMaxIP= a.IntMaxIP 
) y on x.AreaName =y.AreaName 

Answer 2

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
IntMinIP varchar(20),
IntMaxIP varchar(20),
CharMinIP varchar(20),
CharMaxIP varchar(20),
AreaName varchar(20)
)insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('3717939200','3717939455','221.155.64.0','221.155.64.255', '韩国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('3717939456','3718840319','221.155.65.0','221.168.255.255','韩国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('3717201920','3717414655','221.144.0.0', '221.147.62.255', '韩国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('3717414656','3717414911','221.147.63.0','221.147.63.255', '韩国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('50331648',  '67108863',  '3.0.0.0',     '3.255.255.255'   ,'美国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('67108864',  '67829759',  '4.0.0.0',     '4.10.255.255'    ,'美国')
insert into tb(IntMinIP,IntMaxIP,CharMinIP,CharMaxIP,AreaName) values('67829760',  '67895295',  '4.11.0.0',    '4.11.255.255'    ,'美国')select p.* , q.CharMinIP , q.CharMaxIP from
(
select areaname , min(intip) as IntMinIP , max(intip) as IntMaxIP from
(
  select IntMinIP as intip , areaname from tb
  union all
  select IntMaxIP as intip , areaname from tb
) m
group by areaname
) p , 
(
select areaname , min(charip) as CharMinIP , max(charip) as CharMaxIP from
(
  select CharMinIP as charip , areaname from tb
  union all
  select CharMaxIP as charip , areaname from tb
) n
group by areaname
) q
where p.areaname = q.areaname
drop table tbareaname             IntMinIP             IntMaxIP             CharMinIP            CharMaxIP            
-------------------- -------------------- -------------------- -------------------- -------------------- 
韩国                 3717201920           3718840319           221.144.0.0          221.168.255.255
美国                 50331648             67895295             3.0.0.0              4.11.255.255（所影响的行数为 2 行）

Answer 3

create table model(IntMinIP bigint, IntMaxIP bigint, CharMinIP varchar(20), CharMaxIP varchar(20), AreaName varchar(20))
insert model select 3717939200, 3717939455, '221.155.64.0', '221.155.64.255', '韩国'
union all select 3717939456, 3718840319, '221.155.65.0', '221.168.255.255', '韩国'
union all select 3717201920, 3717414655, '221.144.0.0', '221.147.62.255', '韩国'
union all select 3717414656, 3717414911, '221.147.63.0', '221.147.63.255', '韩国'union all select 50331648, 67108863, '3.0.0.0', '3.255.255.255', '美国'
union all select 67108864, 67829759, '4.0.0.0', '4.10.255.255', '美国'
union all select 67829760, 67895295, '4.11.0.0', '4.11.255.255', '美国'select ID=identity(int, 1, 1), * into #T from modelselect 
IntMinIP=(select IntMinIP from #T where AreaName=tmp.AreaName and ID=min(tmp.ID)), 
IntMaxIP=(select IntMaxIP from #T where AreaName=tmp.AreaName and ID=max(tmp.ID)), 
CharMinIP=(select CharMinIP from #T where AreaName=tmp.AreaName and ID=min(tmp.ID)), 
CharMaxIP=(select CharMaxIP from #T where AreaName=tmp.AreaName and ID=max(tmp.ID)), 
AreaName
from #T as tmp
group by AreaName--result
IntMinIP             IntMaxIP             CharMinIP            CharMaxIP            AreaName             
-------------------- -------------------- -------------------- -------------------- -------------------- 
3717939200           3717414911           221.155.64.0         221.147.63.255       韩国
50331648             67895295             3.0.0.0              4.11.255.255         美国(2 row(s) affected)

Answer 4

上面结果错位,调整了一下.areaname  IntMinIP    IntMaxIP    CharMinIP    CharMaxIP
--------- ----------- ----------- ------------ ---------
韩国      3717201920  3718840319  221.144.0.0  221.168.255.255
美国      50331648    67895295    3.0.0.0      4.11.255.255（所影响的行数为 2 行）

Answer 5

不好意思！刚才没排序所以搞错了~
我重新贴一下:
t1:
IntMinIP IntMaxIP CharMinIP  CharMaxIP AreaName
50331648 67108863 3.0.0.0 3.255.255.255 美国
67108864 67829759 4.0.0.0 4.10.255.255 美国
67829760 67895295 4.11.0.0 4.11.255.255 美国
3717201920 3717414655 221.144.0.0 221.147.62.255 韩国
3717414656 3717414911 221.147.63.0 221.147.63.255 韩国
3717939200 3717939455 221.155.64.0 221.155.64.255 韩国
3717939456 3718840319 221.155.65.0 221.168.255.255 韩国
t2:
IntMinIP IntMaxIP CharMinIP  CharMaxIP AreaName
50331648 67895295 3.0.0.0 4.11.255.255 美国
3717201920 3717414911 221.144.0.0 221.147.63.255 韩国
3717939200 3718840319 221.155.64.0 221.168.255.255 韩国

Answer 6

谢谢楼上的几位大虾~偶的失误~我再补充一下
t1,t2的表结构
CREATE TABLE [dbo].[T1](
[IntMinIP] [bigint] NULL,
[IntMaxIP] [bigint] NULL,
[CharMinIP] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL,
[CharMaxIP] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL,
[AreaName] [varchar](128) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

Answer 7

declare @t1 table(IntMinIP numeric(10),IntMaxIP numeric(10),CharMinIP varchar(15),CharMaxIP varchar(15),AreaName varchar(10))
insert into @t1 select '3717939200','3717939455','221.155.64.0','221.155.64.255' ,'韩国'
insert into @t1 select '3717939456','3718840319','221.155.65.0','221.168.255.255','韩国'
insert into @t1 select '3717201920','3717414655','221.144.0.0' ,'221.147.62.255' ,'韩国'
insert into @t1 select '3717414656','3717414911','221.147.63.0','221.147.63.255' ,'韩国'
insert into @t1 select '50331648'  ,'67108863'  ,'3.0.0.0'     ,'3.255.255.255'  ,'美国'
insert into @t1 select '67108864'  ,'67829759'  ,'4.0.0.0'     ,'4.10.255.255'   ,'美国'
insert into @t1 select '67829760'  ,'67895295'  ,'4.11.0.0'    ,'4.11.255.255'   ,'美国'select
    m.IntMinIP,
    min(m.IntMaxIP) as IntMaxIP,
    m.CharMinIP,
    min( right('00'+PARSENAME(n.CharMaxIP,4),3)+'.'+
        +right('00'+PARSENAME(n.CharMaxIP,3),3)+'.'+
        +right('00'+PARSENAME(n.CharMaxIP,2),3)+'.'+
        +right('00'+PARSENAME(n.CharMaxIP,1),3)) as CharMaxIP,
    m.AreaName
from
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMaxIP=a.IntMinIP-1)) m,
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMinIP=a.IntMaxIP+1)) n
where
    m.AreaName=n.AreaName and m.IntMinIP<n.IntMaxIP
group by
    m.IntMinIP,m.CharMinIP,m.AreaName/*
IntMinIP     IntMaxIP     CharMinIP       CharMaxIP       AreaName   
------------ ------------ --------------- --------------- ---------- 
50331648     67108863     3.0.0.0         004.011.255.255 美国
3717201920   3717414655   221.144.0.0     221.147.063.255 韩国
3717939200   3717939455   221.155.64.0    221.168.255.255 韩国
*/

Answer 8

select x.IntMinIP ,y.IntMaxIP ,x.CharMinIP, y.CharMaxIP,x.AreaName 
from 
(
select b.AreaName,IntMinIP,CharMinIP from t1 b
join 
(
select min(IntMinIP) as MiIntIP ,max(IntMaxIP) as MxIntIP ,AreaName
from t1 
group by AreaName
) a on b.AreaName =a.AreaName and b.IntMinIP = a.MiIntIP 
) x 
join 
(
select c.AreaName,IntMaxIP,CharMaxIP from t1 c
join 
(
select min(IntMinIP) as MiIntIP ,max(IntMaxIP) as MxIntIP ,AreaName
from t1 
group by AreaName
) a on c.AreaName =a.AreaName and c.IntMaxIP= a.MxIntIP 
) y on x.AreaName =y.AreaName --改了一下,终于海是好了!IntMinIP             IntMaxIP             CharMinIP            CharMaxIP            AreaName             
-------------------- -------------------- --------------     -------------- -------------------- 
3717201920           3718840319           221.144.0.0          221.168.255.255      韩国
50331648             67895295             3.0.0.0              4.11.255.255         美国（所影响的行数为 2 行）

Answer 9

xiaoku(野蛮人(^v^)) 理解的对的确的考虑IP不连续分配的情况~

Answer 10

完善一下：declare @t1 table(IntMinIP numeric(10),IntMaxIP numeric(10),CharMinIP varchar(15),CharMaxIP varchar(15),AreaName varchar(10))
insert into @t1 select '3717939200','3717939455','221.155.64.0','221.155.64.255' ,'韩国'
insert into @t1 select '3717939456','3718840319','221.155.65.0','221.168.255.255','韩国'
insert into @t1 select '3717201920','3717414655','221.144.0.0' ,'221.147.62.255' ,'韩国'
insert into @t1 select '3717414656','3717414911','221.147.63.0','221.147.63.255' ,'韩国'
insert into @t1 select '50331648'  ,'67108863'  ,'3.0.0.0'     ,'3.255.255.255'  ,'美国'
insert into @t1 select '67108864'  ,'67829759'  ,'4.0.0.0'     ,'4.10.255.255'   ,'美国'
insert into @t1 select '67829760'  ,'67895295'  ,'4.11.0.0'    ,'4.11.255.255'   ,'美国'select
    m.IntMinIP,
    min(m.IntMaxIP) as IntMaxIP,
    m.CharMinIP,
    replace(min( right('  '+PARSENAME(n.CharMaxIP,4),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,3),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,2),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,1),3)),' ','') as CharMaxIP,
    m.AreaName
from
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMaxIP=a.IntMinIP-1)) m,
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMinIP=a.IntMaxIP+1)) n
where
    m.AreaName=n.AreaName and m.IntMinIP<n.IntMaxIP
group by
    m.IntMinIP,m.CharMinIP,m.AreaName/*
IntMinIP     IntMaxIP     CharMinIP       CharMaxIP       AreaName   
------------ ------------ --------------- --------------- ---------- 
50331648     67108863     3.0.0.0         4.11.255.255    美国
3717201920   3717414655   221.144.0.0     221.147.63.255  韩国
3717939200   3717939455   221.155.64.0    221.168.255.255 韩国
*/

Answer 11

再次感谢楼上各位大虾的关注！To libin_ftsafe(子陌红尘：当libin告别ftsafe)：
50331648     67108863     3.0.0.0         4.11.255.255    美国
中的67108863     应该为：67895295
即，4.11.255.255    to bigint对应值：67895295

Answer 12

declare @t1 table(IntMinIP numeric(10),IntMaxIP numeric(10),CharMinIP varchar(15),CharMaxIP varchar(15),AreaName varchar(10))
insert into @t1 select '3717939200','3717939455','221.155.64.0','221.155.64.255' ,'韩国'
insert into @t1 select '3717939456','3718840319','221.155.65.0','221.168.255.255','韩国'
insert into @t1 select '3717201920','3717414655','221.144.0.0' ,'221.147.62.255' ,'韩国'
insert into @t1 select '3717414656','3717414911','221.147.63.0','221.147.63.255' ,'韩国'
insert into @t1 select '50331648'  ,'67108863'  ,'3.0.0.0'     ,'3.255.255.255'  ,'美国'
insert into @t1 select '67108864'  ,'67829759'  ,'4.0.0.0'     ,'4.10.255.255'   ,'美国'
insert into @t1 select '67829760'  ,'67895295'  ,'4.11.0.0'    ,'4.11.255.255'   ,'美国'select
    m.IntMinIP,
    min(n.IntMaxIP) as IntMaxIP,  --有一个笔误，将此处的 m 修改为 n 即可
    m.CharMinIP,
    replace(min( right('  '+PARSENAME(n.CharMaxIP,4),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,3),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,2),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,1),3)),' ','') as CharMaxIP,
    m.AreaName
from
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMaxIP=a.IntMinIP-1)) m,
    (select a.* from @t1 a where not exists(select 1 from @t1 where IntMinIP=a.IntMaxIP+1)) n
where
    m.AreaName=n.AreaName and m.IntMinIP<n.IntMaxIP
group by
    m.IntMinIP,m.CharMinIP,m.AreaName/*
IntMinIP     IntMaxIP     CharMinIP       CharMaxIP       AreaName   
------------ ------------ --------------- --------------- ---------- 
50331648     67895295     3.0.0.0         4.11.255.255    美国
3717201920   3717414911   221.144.0.0     221.147.63.255  韩国
3717939200   3718840319   221.155.64.0    221.168.255.255 韩国
*/

Answer 13

谢谢 子陌兄~
能帮解释下t2中CharMaxIP为什么要通过replace(min( right('  '+PARSENAME(n.CharMaxIP,4),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,3),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,2),3)+'.'+
                +right('  '+PARSENAME(n.CharMaxIP,1),3)),' ','')取得
,min(n.CharMaxIP)这样不行么？（俺很愚，见笑了~）

Answer 14

再次感谢诸位的关注！子陌兄~我已开新贴~请来接分~http://community.csdn.net/Expert/topic/5331/5331362.xml?temp=3.20071E-03