我有一张表ipdz 字段为ipdz_first,ipdz_end,sfmc
表中记录
ipdz_first ipdz_end sfmc
192.168.0.1 192.168.0.255 1
192.168.10.1 192.168.12.255 2
192.168.22.1 192.168.35.255 3请问如何建立一个存储过程可以查询出具体的192.168.11.2 所在记录的sfmc就是说需要查找出具体的192.168.11.2所在的某个ip地址段的字段sfmc的值请问这个存储过程如何写啊?
表中记录
ipdz_first ipdz_end sfmc
192.168.0.1 192.168.0.255 1
192.168.10.1 192.168.12.255 2
192.168.22.1 192.168.35.255 3请问如何建立一个存储过程可以查询出具体的192.168.11.2 所在记录的sfmc就是说需要查找出具体的192.168.11.2所在的某个ip地址段的字段sfmc的值请问这个存储过程如何写啊?
INSERT INTO @ipdz select '192.168.0.1 ','192.168.0.255 ',1
INSERT INTO @ipdz select '192.168.10.1','192.168.12.255',2
INSERT INTO @ipdz select '192.168.22.1','192.168.35.255',3DECLARE @IP VARCHAR(20)
SET @IP='192.168.11.2'SELECT
SFMC
FROM
@ipdz
WHERE
RIGHT('00'+RTRIM(PARSENAME(@IP,4)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(@IP,3)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(@IP,2)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(@IP,1)),3)
BETWEEN
(RIGHT('00'+RTRIM(PARSENAME(ipdz_first,4)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_first,3)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_first,2)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_first,1)),3))
AND
(RIGHT('00'+RTRIM(PARSENAME(ipdz_end ,4)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_end ,3)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_end ,2)),3)+'.'+
RIGHT('00'+RTRIM(PARSENAME(ipdz_end ,1)),3) )/*
SFMC
-----------
2
*/
INSERT INTO @ipdz select '192.168.0.1 ','192.168.0.255 ',1
INSERT INTO @ipdz select '192.168.10.1','192.168.12.255',2
INSERT INTO @ipdz select '192.168.22.1','192.168.35.255',3DECLARE @IP VARCHAR(20)
SET @IP='192.168.11.2'SELECT
SFMC
FROM
@ipdz
WHERE
CAST(PARSENAME(@IP,4) AS BIGINT)*POWER(255,3)+
CAST(PARSENAME(@IP,3) AS BIGINT)*POWER(255,2)+
CAST(PARSENAME(@IP,2) AS BIGINT)*POWER(255,1)+
CAST(PARSENAME(@IP,1) AS BIGINT)
BETWEEN
CAST(PARSENAME(ipdz_first,4) AS BIGINT)*POWER(255,3)+
CAST(PARSENAME(ipdz_first,3) AS BIGINT)*POWER(255,2)+
CAST(PARSENAME(ipdz_first,2) AS BIGINT)*POWER(255,1)+
CAST(PARSENAME(ipdz_first,1) AS BIGINT)
AND
CAST(PARSENAME(ipdz_end ,4) AS BIGINT)*POWER(255,3)+
CAST(PARSENAME(ipdz_end ,3) AS BIGINT)*POWER(255,2)+
CAST(PARSENAME(ipdz_end ,2) AS BIGINT)*POWER(255,1)+
CAST(PARSENAME(ipdz_end ,1) AS BIGINT)/*
SFMC
-----------
2
*/