高分请求一高难SQL语句---IP整合
model:
t1:
IntMinIPIntMaxIPCharMinIPCharMaxIPAreaName
37179392003717939455221.155.64.0221.155.64.255韩国
37179394563718840319221.155.65.0221.168.255.255韩国
37172019203717414655221.144.0.0221.147.62.255韩国
37174146563717414911221.147.63.0221.147.63.255韩国
50331648671088633.0.0.03.255.255.255美国
67108864678297594.0.0.04.10.255.255美国
67829760678952954.11.0.04.11.255.255美国
t2:
IntMinIPIntMaxIPCharMinIPCharMaxIPAreaName
37179392003717414911221.155.64.0221.147.63.255韩国
50331648678952953.0.0.04.11.255.255美国
注释:t1源表，t2结果表
CharMinIP:对应域名（AreaName）的起始IP;
CharMaxIP:对应域名（AreaName）的结束IP;
IntMinIP:CharMinIP的数字表示;
IntMaxIP:CharMaxIP的数字表示.
要求:
将同一域名的连续IP段合成一个网段
例如：t1种 '韩国 '多应的四条记录合成t2中的一条
请高手指点迷津~最佳答案另开贴送分~
提供答案的均给大比例分~
顶帖均给分~
因为时间紧迫（快放年假了）~希望大家积极响应~感激不尽~
[解决办法]
韩国 这条记录的 IntMinIP 应该是 3717201920 巴
[解决办法]
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
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create 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 tb
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 行）
[解决办法]
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 model
select
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)