[问题求解]求符合条件的不包含在表中的行

[syl2000]

已知IP地址和子网掩码，表中已经具有一些符合该IP和掩码条件的特定IP地址，要求符合该IP和掩码要求，又不包括在表中的IP地址。
举例：已知IP范围是（172.16.1.0/255.255.255.0）,表ListIP中已经有172.16.1.1、172.16.1.2、172.16.1.7，现在要把172.16.1.3、172.16.1.4等取出来。
这样的SQL语句应该如何实现？

[zc_0101]

给出表结构，给写数据，贴出需要的结果

[htl258_Tony]

select * from tb where IP like '192.168.1.[1-254]'

[htl258_Tony]

select * from tb where IP like 172.16.1.[1-254]'

[syl2000]

[Quote=引用 1 楼 zc_0101 的回复:]
给出表结构，给写数据，贴出需要的结果
[/Quote]

举例：已知IP范围是（172.16.1.0/255.255.255.0）,表ListIP中已经有172.16.1.1、172.16.1.2、172.16.1.7，ip地址是字符串型。
需要的结果就是：172.16.1.3、172.16.1.4等不包含在表ListIP中，但是又符合IP范围（172.16.1.0/255.255.255.0）的这些行

说得应该够清楚了哦

[东那个升]

这也忒多了吧。。。。。

[jwwyqs]

--下班了帮顶

[--小F--]

需要拆分了再判断 用parsename函数

[老黎]

--写简单一点，只写了掩码必定是255.255.255.0的情况
if object_id('ListIP','U') is not null
    drop table ListIP;
go
create table ListIP(
    IP   varchar(15)
)
go
insert into ListIP 
select '172.16.1.1' union all
select '172.16.1.2' union all
select '172.16.1.7'
go

declare @IP varchar(15)
declare @Tack varchar(15)

set @IP = '172.16.1.0'
set @Tack = '255.255.255.0'

select 
    a.*
from (
		select 
			[AllIP] = left(@IP,len(@IP)-charindex('.',reverse(@IP)) + 1) 
			+ cast(number as varchar)
		from 
			master..spt_values
		where 
			[type] = 'P'
		  and number between 0 and 255
	 ) a
     left join ListIP b
on a.AllIP = b.IP
where b.IP is null
order by cast(parsename(a.AllIP,1) as int)


--结果
/*
AllIP
---------------------------------------------
172.16.1.0
172.16.1.3
172.16.1.4
172.16.1.5
172.16.1.6
172.16.1.8
172.16.1.9
172.16.1.10
172.16.1.11
172.16.1.12
172.16.1.13
172.16.1.14
172.16.1.15
172.16.1.16
172.16.1.17
172.16.1.18
172.16.1.19
172.16.1.20
172.16.1.21
172.16.1.22
172.16.1.23
172.16.1.24
172.16.1.25
172.16.1.26
172.16.1.27
172.16.1.28
172.16.1.29
172.16.1.30
172.16.1.31
172.16.1.32
172.16.1.33
172.16.1.34
172.16.1.35
172.16.1.36
172.16.1.37
172.16.1.38
172.16.1.39
172.16.1.40
172.16.1.41
172.16.1.42
172.16.1.43
172.16.1.44
172.16.1.45
172.16.1.46
172.16.1.47
172.16.1.48
172.16.1.49
172.16.1.50
172.16.1.51
172.16.1.52
172.16.1.53
172.16.1.54
172.16.1.55
172.16.1.56
172.16.1.57
172.16.1.58
172.16.1.59
172.16.1.60
172.16.1.61
172.16.1.62
172.16.1.63
172.16.1.64
172.16.1.65
172.16.1.66
172.16.1.67
172.16.1.68
172.16.1.69
172.16.1.70
172.16.1.71
172.16.1.72
172.16.1.73
172.16.1.74
172.16.1.75
172.16.1.76
172.16.1.77
172.16.1.78
172.16.1.79
172.16.1.80
172.16.1.81
172.16.1.82
172.16.1.83
172.16.1.84
172.16.1.85
172.16.1.86
172.16.1.87
172.16.1.88
172.16.1.89
172.16.1.90
172.16.1.91
172.16.1.92
172.16.1.93
172.16.1.94
172.16.1.95
172.16.1.96
172.16.1.97
172.16.1.98
172.16.1.99
172.16.1.100
172.16.1.101
172.16.1.102
172.16.1.103
172.16.1.104
172.16.1.105
172.16.1.106
172.16.1.107
172.16.1.108
172.16.1.109
172.16.1.110
172.16.1.111
172.16.1.112
172.16.1.113
172.16.1.114
172.16.1.115
172.16.1.116
172.16.1.117
172.16.1.118
172.16.1.119
172.16.1.120
172.16.1.121
172.16.1.122
172.16.1.123
172.16.1.124
172.16.1.125
172.16.1.126
172.16.1.127
172.16.1.128
172.16.1.129
172.16.1.130
172.16.1.131
172.16.1.132
172.16.1.133
172.16.1.134
172.16.1.135
172.16.1.136
172.16.1.137
172.16.1.138
172.16.1.139
172.16.1.140
172.16.1.141
172.16.1.142
172.16.1.143
172.16.1.144
172.16.1.145
172.16.1.146
172.16.1.147
172.16.1.148
172.16.1.149
172.16.1.150
172.16.1.151
172.16.1.152
172.16.1.153
172.16.1.154
172.16.1.155
172.16.1.156
172.16.1.157
172.16.1.158
172.16.1.159
172.16.1.160
172.16.1.161
172.16.1.162
172.16.1.163
172.16.1.164
172.16.1.165
172.16.1.166
172.16.1.167
172.16.1.168
172.16.1.169
172.16.1.170
172.16.1.171
172.16.1.172
172.16.1.173
172.16.1.174
172.16.1.175
172.16.1.176
172.16.1.177
172.16.1.178
172.16.1.179
172.16.1.180
172.16.1.181
172.16.1.182
172.16.1.183
172.16.1.184
172.16.1.185
172.16.1.186
172.16.1.187
172.16.1.188
172.16.1.189
172.16.1.190
172.16.1.191
172.16.1.192
172.16.1.193
172.16.1.194
172.16.1.195
172.16.1.196
172.16.1.197
172.16.1.198
172.16.1.199
172.16.1.200
172.16.1.201
172.16.1.202
172.16.1.203
172.16.1.204
172.16.1.205
172.16.1.206
172.16.1.207
172.16.1.208
172.16.1.209
172.16.1.210
172.16.1.211
172.16.1.212
172.16.1.213
172.16.1.214
172.16.1.215
172.16.1.216
172.16.1.217
172.16.1.218
172.16.1.219
172.16.1.220
172.16.1.221
172.16.1.222
172.16.1.223
172.16.1.224
172.16.1.225
172.16.1.226
172.16.1.227
172.16.1.228
172.16.1.229
172.16.1.230
172.16.1.231
172.16.1.232
172.16.1.233
172.16.1.234
172.16.1.235
172.16.1.236
172.16.1.237
172.16.1.238
172.16.1.239
172.16.1.240
172.16.1.241
172.16.1.242
172.16.1.243
172.16.1.244
172.16.1.245
172.16.1.246
172.16.1.247
172.16.1.248
172.16.1.249
172.16.1.250
172.16.1.251
172.16.1.252
172.16.1.253
172.16.1.254
172.16.1.255

(253 行受影响)


*/

[syl2000]

[Quote=引用 8 楼 kerafan 的回复:]
SQL code

--写简单一点，只写了掩码必定是255.255.255.0的情况
if object_id('ListIP','U') is not null
drop table ListIP;
go
create table ListIP(
IP varchar(15)
)
go
insert into ListIP
select '172.16……
[/Quote]

非常感谢！确实，对于掩码是255.255.255.0类型的，解决的非常好！

[syl2000]

[Quote=引用 8 楼 kerafan 的回复:]
SQL code

--写简单一点，只写了掩码必定是255.255.255.0的情况
if object_id('ListIP','U') is not null
drop table ListIP;
go
create table ListIP(
IP varchar(15)
)
go
insert into ListIP
select '172.16……
[/Quote]

受启发后，解决了问题！

1、自建整数表number，包含number列，该列为从0到65536的整数
2、编写函数f_ip2int和f_int2ip，完成字符串IP地址和整数的相互转换
3、编写如下存储过程：

CREATE procedure mysp_qry_restip @ip char(15),@msk char(15)
as

declare @maxhostnbr bigint
declare @networknbr bigint

set @maxhostnbr = dbo.f_ip2int(@msk) ^ dbo.f_ip2int('255.255.255.255')
set @networknbr = dbo.f_ip2int(@ip) & dbo.f_ip2int(@msk)

select [AllIp]=dbo.f_int2ip(@networknbr+number) from number where number>0 and number<@maxhostnbr and @networknbr+number not in (select [ip]=dbo.f_ip2int(ipaddr) from lt_lan where dbo.f_ip2int(ipaddr)&dbo.f_ip2int(@msk)=dbo.f_ip2int(@ip)&dbo.f_ip2int(@msk)) order by @networknbr+number
GO



结果：可以解决掩码255.255.0.0-255.255.255.x之间的部分，将表lt_lan中符合（ip/msk）条件之外的ip地址选出来