多表联合

目前有4个表（以后可能还要增加），分别为：
表1、General_IP：

系统名单位名起始IP地址终止IP地址匹配用起始IP地址匹配用Str终止IP地址
Sys Co_Name Start_IP End_IP Str_startIP Str_endIP
市委系统市委办公厅 10.0.0.1 10.1.255.254 010000000001 010001255254
省委系统市委监察院 10.2.0.1 10.255.255.254 010002000001 010255255254
（匹配用的IP地址其实和前面的IP地址一样，只是表现形式不同而已，这是为了做字符串判断用，
如:Str_IP between Str_startIP and Str_endIP）

表2、Wavetop_Wsus:

实际IP地址匹配用IP地址机器名最后登陆时间工作ID
IP Str_IP Computer_Name LastSyncTime Job_ID
10.0.1.10 010000001010 wsusserver 2008-1-1 1
10.110.0.1 010110000001 hclddl 2007-12-10 2
......等多条记录

表3、Wavetop_Symantec:

实际IP地址匹配用IP地址机器名最后更新时间工作ID
IP Str_IP Computer_Name LastCheckinTime Job_ID

表3中的数据和表2的查不多，有相同的，也有不同的。

表4、Wavetop_Detail:

实际IP地址匹配用IP地址设备类型设备所在楼号设备所在楼层设备所在房间号工作ID
IP Str_SIP NET_DEV_TYPE NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM Job_ID
10.0.2.1 010000002001 A 1 1 101 1
10.5.1.1 010005001001 B 2 5 503 1

大概的表结构就是这样的，每个表的记录数都不同，每个表的Str_IP都是为了去匹配表1中的IP地址段，
也就是Start_IP和End_IP。可以很容易的看出是为了找匹配的IP段然后取对应的Sys字段（系统名）和Co_Name字段（公司名）。
如果匹配不上，就显示null(空)。每个表的所有记录都要显示出来。然后以Job_ID为最终的查询显示条件，并按Co_Name排序。

最后显示的结构和结果有如下字就行，我主要使用java的rs取其中的数据：

系统单位 IP 机器名最后登陆时间最后更新时间设备的那4个字段工作ID

将每个表中对应的字段数据放入相应的列里就OK了，匹配不上的地方要显示null，决不能丢掉每个表的任何一条记录！

求高手你帮帮忙，我很急，谢谢了！

问题点数：200 回复次数：18 修改删除举报引用回复

加为好友

发送私信

liangCK
小梁(幸福,這麼遠,那麼甜...!)
等级:
可用分等级：贫农
总技术分：44057
总技术分排名：189
2

发表于：2008-03-20 15:31:541楼得分:0

好长..先顶一下.再看.

修改删除举报引用回复

加为好友

发送私信

sdhylj
青锋-SS
等级:
可用分等级：贫农
总技术分：25345
总技术分排名：421
4

发表于：2008-03-20 15:53:262楼得分:0

没人弄啊

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:00:273楼得分:0

SQL code

                        猜测一个,以General_IP为主表左连接其他三个表.
                        select a.*,b.*,c.*,d.* from General_IP a
                        left join Wavetop_Wsus b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Symantec c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Detail c on c.Str_IP between a.Str_startIP and a.Str_endIP

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:01:324楼得分:0

SQL code

                        猜测一个,以General_IP为主表左连接其他三个表.
                        select a.*,b.*,c.*,d.* from General_IP a
                        left join Wavetop_Wsus b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Symantec c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Detail d on d.Str_IP between a.Str_startIP and a.Str_endIP

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:12:305楼得分:0

SQL code

                        [code=SQL]--有结果,D表字段名写错了.是Str_SIP ,不是Str_IP 
                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select a.*,b.*,c.*,d.* from General_IP a
                        left join Wavetop_Wsus b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Symantec c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        left join Wavetop_Detail d on d.Str_SIP between a.Str_startIP and a.Str_endIP
                        drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail

[/code]

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:13:236楼得分:0

注意:上面drop那行内容不要,以免你把表个删除了.
/*
Sys Co_Name Start_IP End_IP Str_startIP Str_endIP IP Str_IP Computer_Name LastSyncTime Job_ID IP Str_IP Computer_Name LastCheckinTime Job_ID IP Str_SIP NET_DEV_TYPE NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM Job_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ---------------- ------------- ------------ -----------
市委系统市委办公厅 10.0.0.1 10.1.255.254 010000000001 010001255254 10.0.1.10 010000001010 wsusserver 2008-01-01 00:00:00.000 1 NULL NULL NULL NULL NULL 10.0.2.1 010000002001 A 1 1 101 1
省委系统市委监察院 10.2.0.1 10.255.255.254 010002000001 010255255254 10.110.0.1 010110000001 hclddl 2007-12-10 00:00:00.000 2 NULL NULL NULL NULL NULL 10.5.1.1 010005001001 B 2 5 503 1

（所影响的行数为 2 行）
*/

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:17:427楼得分:0

用full join

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:18:478楼得分:0

SQL code

                        --用full join ,最后一个表我加了一条数据.
                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        insert into Wavetop_Detail values('110.5.1.1', '110005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select a.*,b.*,c.*,d.* from General_IP a
                        full join Wavetop_Wsus b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        full join Wavetop_Symantec c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        full join Wavetop_Detail d on d.Str_SIP between a.Str_startIP and a.Str_endIP
                        --drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail
                        
                        /*
                        Sys                  Co_Name              Start_IP             End_IP               Str_startIP          Str_endIP            IP                   Str_IP               Computer_Name        LastSyncTime                                           Job_ID      IP                   Str_IP               Computer_Name        LastCheckinTime                                        Job_ID      IP                   Str_SIP              NET_DEV_TYPE         NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM Job_ID
                        -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ---------------- ------------- ------------ -----------
                        市委系统                 市委办公厅                10.0.0.1             10.1.255.254         010000000001         010001255254         10.0.1.10            010000001010         wsusserver           2008-01-01 00:00:00.000                                1           NULL                 NULL                 NULL                 NULL                                                   NULL        10.0.2.1             010000002001         A                    1                1             101          1
                        省委系统                 市委监察院                10.2.0.1             10.255.255.254       010002000001         010255255254         10.110.0.1           010110000001         hclddl               2007-12-10 00:00:00.000                                2           NULL                 NULL                 NULL                 NULL                                                   NULL        10.5.1.1             010005001001         B                    2                5             503          1
                        NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                                                   NULL        NULL                 NULL                 NULL                 NULL                                                   NULL        110.5.1.1            110005001001         B                    2                5             503          1
                        （所影响的行数为 3 行）
                        */

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:25:289楼得分:50

SQL code

                        --这样就行了.
                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select a.*,b.*,c.*,d.* from General_IP a
                        full join Wavetop_Wsus  b on b.Str_IP between a.Str_startIP and a.Str_endIP and b.job_id = 1
                        full join Wavetop_Symantec c on c.Str_IP between a.Str_startIP and a.Str_endIP and c.job_id = 1
                        full join Wavetop_Detail  d on d.Str_SIP between a.Str_startIP and a.Str_endIP and d.job_id = 1
                        drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail
                        /*
                        Sys                  Co_Name              Start_IP             End_IP               Str_startIP          Str_endIP            IP                   Str_IP               Computer_Name        LastSyncTime                                           Job_ID      IP                   Str_IP               Computer_Name        LastCheckinTime                                        Job_ID      IP                   Str_SIP              NET_DEV_TYPE         NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM Job_ID
                        -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ---------------- ------------- ------------ -----------
                        市委系统                 市委办公厅                10.0.0.1             10.1.255.254         010000000001         010001255254         10.0.1.10            010000001010         wsusserver           2008-01-01 00:00:00.000                                1           NULL                 NULL                 NULL                 NULL                                                   NULL        10.0.2.1             010000002001         A                    1                1             101          1
                        省委系统                 市委监察院                10.2.0.1             10.255.255.254       010002000001         010255255254         NULL                 NULL                 NULL                 NULL                                                   NULL        NULL                 NULL                 NULL                 NULL                                                   NULL        10.5.1.1             010005001001         B                    2                5             503          1
                        NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 10.110.0.1           010110000001         hclddl               2007-12-10 00:00:00.000                                2           NULL                 NULL                 NULL                 NULL                                                   NULL        NULL                 NULL                 NULL                 NULL             NULL          NULL         NULL
                        （所影响的行数为 3 行）
                        */

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 16:56:0510楼得分:50

语句应该是没错的了.
现在不是对不对的问题了.
而是你的IP不能通过这样来计算大小.
得用下面的方法.

/*
判断IP地址在表中两个IP段的位置
(爱新觉罗.毓华 2007-12-31 广东深圳答csdn zyciis630)
原帖地址：http://topic.csdn.net/u/20071230/22/f4c19d7f-194d-47c9-9840-474ca79c782f.html
*/
/*问题描述
比我我IP:220.113.49.43
然后我有如下的数据库
那么我如何正确的查询出我的IP是符合哪一个数据呢
StartIP EndIP Area
220.112.208.0 220.112.255.255 湖北省武汉市长城宽带
220.113.0.0 220.113.48.255 北京市长城宽带
220.113.49.0 220.113.63.255 广东省广州市长城宽带
220.113.64.0 220.113.79.22 湖北省武汉市长城宽带
220.113.79.23 220.113.79.23 湖北省宜昌市长城宽带刘家大堰小区
220.113.79.24 220.113.81.208 湖北省武汉市长城宽带
220.113.81.209 220.113.81.209 湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司
220.113.81.210 220.113.107.52 湖北省武汉市长城宽带
220.113.107.53 220.113.107.53 湖北省武汉市徐东路逸居苑小区
220.113.107.54 220.113.122.182 湖北省武汉市长城宽带
220.113.122.183 220.113.122.183 湖北省武汉市长城宽带湖北大学校内
220.113.122.184 220.113.127.255 湖北省武汉市长城宽带
*/

--------------------------------------------------------------
create table tb(StartIP varchar(50),EndIP varchar(50),Area varchar(100))
insert into tb values('220.112.208.0' ,'220.112.255.255','湖北省武汉市长城宽带')
insert into tb values('220.113.0.0' ,'220.113.48.255' ,'北京市长城宽带')
insert into tb values('220.113.49.0' ,'220.113.63.255' ,'广东省广州市长城宽带')
insert into tb values('220.113.64.0' ,'220.113.79.22' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.79.23' ,'220.113.79.23' ,'湖北省宜昌市长城宽带刘家大堰小区')
insert into tb values('220.113.79.24' ,'220.113.81.208' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.81.209' ,'220.113.81.209' ,'湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司 ')
insert into tb values('220.113.81.210' ,'220.113.107.52' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.107.53' ,'220.113.107.53' ,'湖北省武汉市徐东路逸居苑小区')
insert into tb values('220.113.107.54' ,'220.113.122.182','湖北省武汉市长城宽带')
insert into tb values('220.113.122.183','220.113.122.183','湖北省武汉市长城宽带湖北大学校内')
insert into tb values('220.113.122.184','220.113.127.255','湖北省武汉市长城宽带')
go

declare @ip as varchar(50)
set @ip = '220.113.49.43'

select startip , endip , area
from tb
where
cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) > =
cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint) and
cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) <=
cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint)

drop table tb

/*
startip endip area
-------------- -------------- --------------------
220.113.49.0 220.113.63.255 广东省广州市长城宽带

(1 行受影响)
*/
----

以下用函数实现(csdn happyflystone完成)

CREATE FUNCTION dbo.f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(@re)
END
GO

declare @t table(StartIP varchar(15),EndIP varchar(15),Area varchar(100))
insert @t select '220.112.208.0','220.112.255.255','湖北省武汉市长城宽带 '
insert @t select '220.113.0.0','220.113.48.255','北京市长城宽带'
insert @t select '220.113.49.0','220.113.63.255','广东省广州市长城宽带 '
insert @t select '220.113.64.0','220.113.79.22','湖北省武汉市长城宽带'
insert @t select '220.113.79.23','220.113.79.23','湖北省宜昌市长城宽带刘家大堰小区'
insert @t select '220.113.79.24','220.113.81.208','湖北省武汉市长城宽带'
insert @t select '220.113.81.209','220.113.81.209','湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司'
insert @t select '220.113.81.210','220.113.107.52','湖北省武汉市长城宽带 '
insert @t select '220.113.107.53','220.113.107.53','湖北省武汉市徐东路逸居苑小区'
insert @t select '220.113.107.54','220.113.122.182','湖北省武汉市长城宽带'
insert @t select '220.113.122.183','220.113.122.183','湖北省武汉市长城宽带湖北大学校内'
insert @t select '220.113.122.184','220.113.127.255','湖北省武汉市长城宽带'
declare @ip varchar(15)
set @ip = '220.113.49.43'
select *
from @t
where dbo.f_IP2Int(@ip) between dbo.f_IP2Int(StartIP) and dbo.f_IP2Int(EndIP)
drop function f_IP2Int

/*

StartIP EndIP Area
-------------------------------------------------
220.113.49.0 220.113.63.255 广东省广州市长城宽带

（所影响的行数为 1 行）
*/

---
IP为'220.113.53.221'
insert into tb values('220.113.49.0' ,'220.113.63.255' ,'广东省广州市长城宽带')
insert into tb values('220.113.53.0' ,'220.113.53.255' ,'广东省广州市东兴小区长城宽带')
也就是有大区间包含小区间。

[code=SQL]create table tb(StartIP varchar(50),EndIP varchar(50),Area varchar(100))
insert into tb values('220.112.208.0' ,'220.112.255.255','湖北省武汉市长城宽带')
insert into tb values('220.113.0.0' ,'220.113.48.255' ,'北京市长城宽带')
insert into tb values('220.113.49.0' ,'220.113.63.255' ,'广东省广州市长城宽带')
insert into tb values('220.113.53.0' ,'220.113.53.255' ,'广东省广州市东兴小区长城宽带')
insert into tb values('220.113.64.0' ,'220.113.79.22' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.79.23' ,'220.113.79.23' ,'湖北省宜昌市长城宽带刘家大堰小区')
insert into tb values('220.113.79.24' ,'220.113.81.208' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.81.209' ,'220.113.81.209' ,'湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司 ')
insert into tb values('220.113.81.210' ,'220.113.107.52' ,'湖北省武汉市长城宽带')
insert into tb values('220.113.107.53' ,'220.113.107.53' ,'湖北省武汉市徐东路逸居苑小区')
insert into tb values('220.113.107.54' ,'220.113.122.182','湖北省武汉市长城宽带')
insert into tb values('220.113.122.183','220.113.122.183','湖北省武汉市长城宽带湖北大学校内')
insert into tb values('220.113.122.184','220.113.127.255','湖北省武汉市长城宽带')
go

declare @ip as varchar(50)
set @ip = '220.113.53.221'

select a.*
from tb a,
(
select ipscore = min(cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint) -
cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint))
from tb
where
cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) > =
cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint) and
cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) <=
cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint)
) b
where cast(PARSENAME(a.endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.endip , 2) as bigint) * 256 + cast(PARSENAME(a.endip , 1) as bigint) -
cast(PARSENAME(a.startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.startip , 2) as bigint) * 256 + cast(PARSENAME(a.startip , 1) as bigint)
= b.ipscore

drop table tb
/*
StartIP EndIP Area
--------------------------------------------------------------
220.113.53.0 220.113.53.255 广东省广州市东兴小区长城宽带

(1 行受影响)
*/

修改删除举报引用回复

加为好友

发送私信

Inspect2008
征服自己
等级:
可用分等级：中农
总技术分：521
总技术分排名：31504

发表于：2008-03-20 17:23:4411楼得分:10

left join

修改删除举报引用回复

加为好友

发送私信

dawugui
爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
等级:
可用分等级：贫农
总技术分：130550
总技术分排名：29
3

3

发表于：2008-03-20 17:25:5212楼得分:10

SQL code

                        那你换为这样再看看结果对不对?
                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select a.*,b.*,c.*,d.* from General_IP a
                        full join (select * from Wavetop_Wsus where job_id = 1) b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        full join (select * from Wavetop_Symantec where job_id = 1) c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        full join (select * from Wavetop_Detail where job_id = 1)  d on d.Str_SIP between a.Str_startIP and a.Str_endIP
                        drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail
                        /*
                        Sys                  Co_Name              Start_IP             End_IP               Str_startIP          Str_endIP            IP                   Str_IP               Computer_Name        LastSyncTime                                           Job_ID      IP                   Str_IP               Computer_Name        LastCheckinTime                                        Job_ID      IP                   Str_SIP              NET_DEV_TYPE         NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM Job_ID
                        -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ------------------------------------------------------ ----------- -------------------- -------------------- -------------------- ---------------- ------------- ------------ -----------
                        市委系统                 市委办公厅                10.0.0.1             10.1.255.254         010000000001         010001255254         10.0.1.10            010000001010         wsusserver           2008-01-01 00:00:00.000                                1           NULL                 NULL                 NULL                 NULL                                                   NULL        10.0.2.1             010000002001         A                    1                1             101          1
                        省委系统                 市委监察院                10.2.0.1             10.255.255.254       010002000001         010255255254         NULL                 NULL                 NULL                 NULL                                                   NULL        NULL                 NULL                 NULL                 NULL                                                   NULL        10.5.1.1             010005001001         B                    2                5             503          1
                        （所影响的行数为 2 行）
                        */

修改删除举报引用回复

加为好友

发送私信

JiangHongTao

等级:
可用分等级：乞丐
总技术分：5902
总技术分排名：3622

发表于：2008-03-20 17:28:0813楼得分:0

SQL code

                        select* from General_IP a
                        full join (select * from  Wavetop_Wsus where job_id = 1)  b on b.Str_IP between a.Str_startIP and a.Str_endIP
                        full join (select * from  Wavetop_Symantec where job_id=1)  c on c.Str_IP between a.Str_startIP and a.Str_endIP
                        full join (select * from  Wavetop_Detail  where job_id = 1) d on d.Str_SIP between a.Str_startIP and a.Str_endIP

修改删除举报引用回复

加为好友

发送私信

JiangHongTao

等级:
可用分等级：乞丐
总技术分：5902
总技术分排名：3622

发表于：2008-03-20 19:42:2914楼得分:40

对表多条件全连接需要构建多级子查询进行连接才能对的正确的结果。
下面的语句能够符合你的要求。

SQL code

                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select e.sys 系统,e.co_name 单位,f.*
                        from General_IP e full join
                        (select isnull(c.ip,d.ip) ip,name 计算机名,lastsynctime 最后登录时间,lastcheckintime 最后更新时间,NET_DEV_TYPE,NET_DEV_BUILDING
                        ,NET_DEV_FLOOR, NET_DEV_ROOM,isnull(c.job_id,d.job_id) 工作ID,isnull(c.str_ip,d.str_sip) str_ip
                        from (select isnull(a.ip,b.ip) ip,isnull(a.computer_name,b.computer_name) name,isnull(a.job_id,b.job_id) job_id
                        ,lastsynctime,lastcheckintime,isnull(a.str_ip,b.str_ip) str_ip
                        from Wavetop_Wsus  a
                        full join Wavetop_Symantec  b on a.ip = b.ip and a.job_id = b.job_id ) c
                        full join  Wavetop_Detail d on c.ip = d.ip and c.job_id=d.job_id
                        ) f
                        on str_ip between Str_startIP and  Str_endIP
                        go
                        drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail
                        /*
                        系统                   单位                   ip                   计算机名                 最后登录时间                                                 最后更新时间                                                 NET_DEV_TYPE         NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM 工作ID        str_ip
                        -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ -------------------- ---------------- ------------- ------------ ----------- --------------------
                        市委系统                 市委办公厅                10.0.1.10            wsusserver           2008-01-01 00:00:00.000                                NULL                                                   NULL                 NULL             NULL          NULL         1           010000001010
                        市委系统                 市委办公厅                10.0.2.1             NULL                 NULL                                                   NULL                                                   A                    1                1             101          1           010000002001
                        省委系统                 市委监察院                10.110.0.1           hclddl               2007-12-10 00:00:00.000                                NULL                                                   NULL                 NULL             NULL          NULL         2           010110000001
                        省委系统                 市委监察院                10.5.1.1             NULL                 NULL                                                   NULL                                                   B                    2                5             503          1           010005001001
                        */

修改删除举报引用回复

加为好友

发送私信

SwjComber
SwjComber(石万杰)
等级:
可用分等级：贫农
总技术分：188
总技术分排名：62186

发表于：2008-03-21 11:24:4815楼得分:0

谢谢大家的帮助，虽然问题还是存在，给分先！
问题是如果后面三个表中有相同IP地址的，要在最后的结果里显示在同一行

修改删除举报引用回复

加为好友

发送私信

JiangHongTao

等级:
可用分等级：乞丐
总技术分：5902
总技术分排名：3622

发表于：2008-03-21 11:26:3316楼得分:0

引用 15 楼 SwjComber 的回复:
谢谢大家的帮助，虽然问题还是存在，给分先！
问题是如果后面三个表中有相同IP地址的，要在最后的结果里显示在同一行

14L应该满足条件呀。

修改删除举报引用回复

加为好友

发送私信

JiangHongTao

等级:
可用分等级：乞丐
总技术分：5902
总技术分排名：3622

发表于：2008-03-21 11:30:0217楼得分:0

问题是你的JOB不同呀，如果忽略JOB就可以合并IP，但JOB取哪个呢？

修改删除举报引用回复

加为好友

发送私信

JiangHongTao

等级:
可用分等级：乞丐
总技术分：5902
总技术分排名：3622

发表于：2008-03-21 11:33:1118楼得分:40

下面的'10.0.1.11' 由于JOB_ID相同不就合并了吗！

SQL code

                        create table General_IP (Sys varchar(20), Co_Name varchar(20), Start_IP varchar(20), End_IP varchar(20), Str_startIP varchar(20), Str_endIP varchar(20))
                        insert into General_IP values('市委系统', '市委办公厅', '10.0.0.1', '10.1.255.254' , '010000000001' , '010001255254')
                        insert into General_IP values('省委系统', '市委监察院', '10.2.0.1', '10.255.255.254', '010002000001' , '010255255254')
                        create table Wavetop_Wsus(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastSyncTime datetime , Job_ID int)
                        insert into Wavetop_Wsus values('10.0.1.11' , '010000001011', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.0.1.10' , '010000001010', 'wsusserver' , '2008-1-1', 1 )
                        insert into Wavetop_Wsus values('10.110.0.1', '010110000001', 'hclddl' , '2007-12-10', 2 )
                        create table Wavetop_Symantec(IP varchar(20), Str_IP varchar(20), Computer_Name varchar(20), LastCheckinTime datetime, Job_ID int)
                        insert into Wavetop_Symantec values('10.0.1.11' , '010000001011', 'wsusserver' , '2008-1-1', 1 )
                        create table Wavetop_Detail(IP varchar(20), Str_SIP varchar(20), NET_DEV_TYPE varchar(20), NET_DEV_BUILDING int, NET_DEV_FLOOR int, NET_DEV_ROOM int, Job_ID int)
                        insert into Wavetop_Detail values('10.0.1.11','010000001011', 'A', 2, 31 , 101 , 1 )
                        insert into Wavetop_Detail values('10.0.2.1', '010000002001', 'A', 1 , 1 , 101 , 1 )
                        insert into Wavetop_Detail values('10.5.1.1', '010005001001', 'B', 2 , 5 , 503 , 1 )
                        go
                        select e.sys 系统,e.co_name 单位,f.*
                        from General_IP e full join
                        (select isnull(c.ip,d.ip) ip,name 计算机名,lastsynctime 最后登录时间,lastcheckintime 最后更新时间,NET_DEV_TYPE,NET_DEV_BUILDING
                        ,NET_DEV_FLOOR, NET_DEV_ROOM,isnull(c.job_id,d.job_id) 工作ID,isnull(c.str_ip,d.str_sip) str_ip
                        from (select isnull(a.ip,b.ip) ip,isnull(a.computer_name,b.computer_name) name,isnull(a.job_id,b.job_id) job_id
                        ,lastsynctime,lastcheckintime,isnull(a.str_ip,b.str_ip) str_ip
                        from Wavetop_Wsus  a
                        full join Wavetop_Symantec  b on a.ip = b.ip and a.job_id = b.job_id ) c
                        full join  Wavetop_Detail d on c.ip = d.ip and c.job_id=d.job_id
                        ) f
                        on str_ip between Str_startIP and  Str_endIP
                        go
                        drop table General_IP,Wavetop_Wsus,Wavetop_Symantec,Wavetop_Detail
                        /*
                        系统                   单位                   ip                   计算机名                 最后登录时间                                                 最后更新时间                                                 NET_DEV_TYPE         NET_DEV_BUILDING NET_DEV_FLOOR NET_DEV_ROOM 工作ID        str_ip
                        -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ -------------------- ---------------- ------------- ------------ ----------- --------------------
                        市委系统                 市委办公厅                10.0.1.11            wsusserver           2008-01-01 00:00:00.000                                2008-01-01 00:00:00.000                                A                    2                31            101          1           010000001011
                        市委系统                 市委办公厅                10.0.1.10            wsusserver           2008-01-01 00:00:00.000                                NULL                                                   NULL                 NULL             NULL          NULL         1           010000001010
                        市委系统                 市委办公厅                10.0.2.1             NULL                 NULL                                                   NULL                                                   A                    1                1             101          1           010000002001
                        省委系统                 市委监察院                10.110.0.1           hclddl               2007-12-10 00:00:00.000                                NULL                                                   NULL                 NULL             NULL          NULL         2           010110000001
                        省委系统                 市委监察院                10.5.1.1             NULL                 NULL                                                   NULL                                                   B                    2                5             503          1           010005001001
                        */

posted @ 2009-04-01 16:32 张立余阅读(330) 评论(0) 收藏举报

刷新页面返回顶部