SQL语句的求助
本帖最后由 zhangyisc 于 2013-11-22 10:22:48 编辑
IP地址
A表：
no address
1 1.1.1.1
2 221.10.5.6
3 19.2.5.3
4 11.5.6.17
B表：
corp address
chengdu 1.1.1.0/24
mianyang11.2.6.0/23
neijiang221.10.5.0/22
ziyang19.2.5.0/21
得出结果
在A表增加一列 corp
no address corp
11.1.1.1chengdu
2221.10.5.6 neijiang
319.2.5.3ziyang
411.5.6.17mianyang
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-22 10:44:08
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([no] int,[address] varchar(10))
insert [A]
select 1,'1.1.1.1' union all
select 2,'221.10.5.6' union all
select 3,'19.2.5.3' union all
select 4,'11.2.7.17'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([corp] varchar(8),[address] varchar(13))
insert [B]
select 'chengdu','1.1.1.0/24' union all
select 'mianyang','11.2.6.0/23' union all
select 'neijiang','221.10.5.0/22' union all
select 'ziyang','19.2.5.0/21'
--------------开始查询--------------------------
select [no],[address],corp from [A] INNER JOIN (select corp,RTRIM(PARSENAME([address],4)+'.'+PARSENAME([address],3)) [BADDRESS] from [B]) b ON a.[ADDRESS] LIKE b.Baddress+'%'
----------------结果----------------------------
/*
no address corp
----------- ---------- --------
1 1.1.1.1 chengdu
4 11.2.7.17 mianyang
2 221.10.5.6 neijiang
3 19.2.5.3 ziyang
*/
