我有个ip库表想要把部分ip段合并, ip值用的bigint存放的
begin end address IPS
123123123 123123122 江苏 联通
123123023 123123123 浙江 电信
123123124 123123223 浙江 电信
123123224 123123324 浙江 电信
123123328 123125324 浙江 电信第二, 三,四行, 前一行的end + 1 = 后一行的begin, address, IPS也一样
所打算存成
123123023 123123324 浙江 电信如果后一行的begin值与上一行的end值不是相差1,或两行adress值或IPS值不同,不合并想写个sql语句合并几行。
现在表中的begin值是有序的, 依次增大,如果无序能不能写个sql也达到上面的效果
begin end address IPS
123123123 123123122 江苏 联通
123123023 123123123 浙江 电信
123123124 123123223 浙江 电信
123123224 123123324 浙江 电信
123123328 123125324 浙江 电信第二, 三,四行, 前一行的end + 1 = 后一行的begin, address, IPS也一样
所打算存成
123123023 123123324 浙江 电信如果后一行的begin值与上一行的end值不是相差1,或两行adress值或IPS值不同,不合并想写个sql语句合并几行。
现在表中的begin值是有序的, 依次增大,如果无序能不能写个sql也达到上面的效果
group by b1.address,b1.IPs
其它几行也是。是整数不过数位比较长。
一个ipcity.sql文件,347k,我用sqlyog导出来的。
3 20 16777472 16778239 电信
4 (NULL) 16778240 16779263
5 11 16779264 16781311 电信
6 (NULL) 16781312 16785407
7 11 16785408 16793599 电信
8 (NULL) 16793600 16842751
10 20 16842752 16843007 电信
11 (NULL) 16843008 16843263
12 20 16843264 16844799 电信
13 11 16844800 16859135 电信
14 (NULL) 16859136 16908287
16 20 16908288 16908799 电信
17 81 16908800 16909055 北龙中网(北京)科技有限责任公司
18 (NULL) 16909056 16909311
21 20 16909312 16910335 电信
22 11 16910336 16941055 电信
===================================================
其实这是个我从网上抓的一个IP库,有IP开始字段和结束字段的. 我没写出来, 我建的这个表数据有重复字段的, 这个bigint字段是为了方便比较的。就是现在
有ip
178.178.178.1 - 178.178.178.20 江苏 电信
178.178.178.21 - 178.178.178.31 江苏 电信
这两条数据可以合成
178.178.178.1 - 178.178.178.31 江苏 电信只有相连两行的 运营商和地直完全相同才能合
这会儿在用程序在读出来慢慢的比较
52 电信 2222 976822272 976824346
53 电信 2222 976824407 976824450
54 电信 2222 976828515 976828938
55 电信 2222 976928768 976932607比如2222,结果是否正确
比如2210,结果是否正确
bz ips city min(begin) max(end)
44 电信 2210 976748544 976757621
45 电信 2210 976758272 976760319
46 电信 2210 976765936 976768002
47 电信 2210 976770203 976770392
48 电信 2210 976775811 976778626
SET @a=0;
SET @b=0;
CREATE TABLE tt AS SELECT *,@b:=@b+1 AS newid FROM `ipcity` ORDER BY IPS,city;
SET @b=0;CREATE TABLE tt1 AS
SELECT @a:=IF(@b=newid-1 ,@a,@a+1) AS bz ,@b:=newid,a.*
FROM (
SELECT b1.* FROM tt b1 LEFT JOIN tt b2 ON b1.ips=b2.ips AND b1.`city`=b2.`city` AND b1.end+1=b2.begin AND b1.newid<b2.newid WHERE b2.id IS NOT NULL ) a;SELECT bz,ips,city,MIN(BEGIN),MAX(END) FROM tt1 GROUP BY bz,ips,city ORDER BY ips,city;节后再看看