F1 F2 F3
--------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 32 1 1
2 1 2
2 1 3
2 2 1
2 2 2
2 2 3
2 3 1
2 3 2
2 3 3...10 1 1
10 1 2
10 1 3
10 2 1
10 2 2
10 2 3
10 3 1
10 3 2
10 3 3给定的条件是:想取
1 1 1
2 1 1
之间的数据,也就是前面的9条数据。select f1,f2,f3
from t1
where (f1 >= 1 and f1 < 2) and
(f2 >= 1 and f2 < 1) and
(f3 >= 1 and f3 < 1)
order by f1,f2,f3
这样肯定没有结果。这样的SQL应该如何写呢,要考虑f1,f2,f3有可能是varchar或者number型
--------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 32 1 1
2 1 2
2 1 3
2 2 1
2 2 2
2 2 3
2 3 1
2 3 2
2 3 3...10 1 1
10 1 2
10 1 3
10 2 1
10 2 2
10 2 3
10 3 1
10 3 2
10 3 3给定的条件是:想取
1 1 1
2 1 1
之间的数据,也就是前面的9条数据。select f1,f2,f3
from t1
where (f1 >= 1 and f1 < 2) and
(f2 >= 1 and f2 < 1) and
(f3 >= 1 and f3 < 1)
order by f1,f2,f3
这样肯定没有结果。这样的SQL应该如何写呢,要考虑f1,f2,f3有可能是varchar或者number型
cast(f1 as int)?
from t1
where f1=1
order by f1,f2,f3
select f1,f2,f3
from t1
where cast(f1 as int)=1
order by f1,f2,f3
from t1
where (cast(f1 as int) between 1 and 2) and
(cast(f1 as int) = 1) and
(cast(f1 as int) = 1)
order by cast(f1 as int) , cast(f2 as int) , cast(f3 as int)select f1,f2,f3
from t1
where (cast(f1 as varchar) between '1' and '2') and
(cast(f1 as varchar) = '1') and
(cast(f1 as varchar) = '1')
order by cast(f1 as varchar) , cast(f2 as varchar) , cast(f3 as varchar)
select
*
from
t
where
right('00'+rtrim(F1),3)+right('00'+rtrim(F2),3)+right('00'+rtrim(F3),3)
between
right('00'+rtrim(1),3)+right('00'+rtrim(1),3)+right('00'+rtrim(1),3)
and
right('00'+rtrim(2),3)+right('00'+rtrim(1),3)+right('00'+rtrim(1),3)
declare @t table(F1 int,F2 int,F3 int)
insert into @t select 1,1,1
insert into @t select 1,1,2
insert into @t select 1,1,3
insert into @t select 1,2,1
insert into @t select 1,2,2
insert into @t select 1,2,3
insert into @t select 1,3,1
insert into @t select 1,3,2
insert into @t select 1,3,3
insert into @t select 2,1,1
insert into @t select 2,1,2
insert into @t select 2,1,3
insert into @t select 2,2,1
insert into @t select 2,2,2
insert into @t select 2,2,3
insert into @t select 2,3,1
insert into @t select 2,3,2
insert into @t select 2,3,3
select
*
from
@t
where
right('00'+rtrim(F1),3)+right('00'+rtrim(F2),3)+right('00'+rtrim(F3),3)>=right('00'+rtrim(1),3)+right('00'+rtrim(1),3)+right('00'+rtrim(1),3)
and
right('00'+rtrim(F1),3)+right('00'+rtrim(F2),3)+right('00'+rtrim(F3),3)< right('00'+rtrim(2),3)+right('00'+rtrim(1),3)+right('00'+rtrim(1),3)/*
F1 F2 F3
----------- ----------- -----------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
*/
select *
from tb
where (cast(F1 as int)*10000+cast(F2 as int)*100+cast(F3 as int)*1) between 10101 and 20101
order by F1,F2,F3
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([F1] int,[F2] int,[F3] int)
insert [tb]
select 1,1,1 union all
select 1,1,2 union all
select 1,1,3 union all
select 1,2,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,3,1 union all
select 1,3,2 union all
select 1,3,3 union all
select 2,1,1 union all
select 2,1,2 union all
select 2,1,3 union all
select 2,2,1 union all
select 2,2,2 union all
select 2,2,3 union all
select 2,3,1 union all
select 2,3,2 union all
select 2,3,3 union all
select 10,1,1 union all
select 10,1,2 union all
select 10,1,3 union all
select 10,2,1 union all
select 10,2,2 union all
select 10,2,3 union all
select 10,3,1 union all
select 10,3,2 union all
select 10,3,3
---查询---
select top 9 * from [tb]
order by cast(f1 as int)*100+cast(f2 as int)*10+cast(f3 as int)---结果---
F1 F2 F3
----------- ----------- -----------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3(所影响的行数为 9 行)
2 用转换的:cast(f1 as int)。f1,f2,f3不一定都是数值型,有可能是字符型,比如aabb,ddcc等,
3 用*1000,*100方法的,同2爱新觉罗的方法,我来试一下
如果只考虑数值型的,可以加一个条件 isnumeric(col)=1 来判断
insert @tb
select 1,1,1 union all
select 1,1,2 union all
select 1,1,3 union all
select 1,2,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,3,1 union all
select 1,3,2 union all
select 1,3,3 union all
select 2,1,1 union all
select 2,1,2 union all
select 2,1,3 union all
select 2,2,1 union all
select 2,2,2 union all
select 2,2,3 union all
select 2,3,1 union all
select 2,3,2 union all
select 2,3,3 union all
select 10,1,1 union all
select 10,1,2 union all
select 10,1,3 union all
select 10,2,1 union all
select 10,2,2 union all
select 10,2,3 union all
select 10,3,1 union all
select 10,3,2 union all
select 10,3,3
declare @f11 int,@f12 int,@f21 int,@f22 int,@f31 int,@f32 intprint '1 1 1 -- 2 1 1 '
select @f11=1,@f12=2,@f21=1,@f22=1,@f31=1,@f32=1select * from @tb
where f1 between @f11 and @f12
and not (f1=@f11 and f2<@f21)
and not (f1=@f11 and f2=@f21 and f3<@f31)
and not (f1=@f12 and f2>@f22)
and not (f1=@f12 and f2=@f22 and f3>=@f32)print '1 1 1 -- 1 2 3 '
select @f11=1,@f12=1,@f21=1,@f22=2,@f31=1,@f32=3select * from @tb
where f1 between @f11 and @f12
and not (f1=@f11 and f2<@f21)
and not (f1=@f11 and f2=@f21 and f3<@f31)
and not (f1=@f12 and f2>@f22)
and not (f1=@f12 and f2=@f22 and f3>=@f32)
print '1 2 3 -- 10 2 2 '
select @f11=1,@f12=10,@f21=2,@f22=2,@f31=3,@f32=2select * from @tb
where f1 between @f11 and @f12
and not (f1=@f11 and f2<@f21)
and not (f1=@f11 and f2=@f21 and f3<@f31)
and not (f1=@f12 and f2>@f22)
and not (f1=@f12 and f2=@f22 and f3>=@f32)--结果
1 1 1 -- 2 1 1
F1 F2 F3
----------- ----------- -----------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3(9 行受影响)1 1 1 -- 1 2 3
F1 F2 F3
----------- ----------- -----------
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2(5 行受影响)1 2 3 -- 10 2 2
F1 F2 F3
----------- ----------- -----------
1 2 3
1 3 1
1 3 2
1 3 3
2 1 1
2 1 2
2 1 3
2 2 1
2 2 2
2 2 3
2 3 1
2 3 2
2 3 3
10 1 1
10 1 2
10 1 3
10 2 1(17 行受影响)
f1(数值型) f2(字符型) f3(数值型) f4(字符型)
-----------------------------------------------
1 10aa 1 10bb
2 10aa 1 10bb可以这么写,初步看是满足要求的:
SELECT *
FROM t1
WHERE to_char(lpad(F1,15,' '))||','||F2||','||to_char(lpad(F3,15,' '))||','||F4 >= lpad(1,15,' ')||',10aa,'||lpad(1,15,' ')||',10bb' and
to_char(lpad(F1,15,' '))||','||F2||','||to_char(lpad(F3,15,' '))||','||F4 < lpad(2,15,' ')||',10aa,'||lpad(1,15,' ')||',10bb'
ORDER BY F1,F2,F3,F4
to_char是oracle用法,意思number-->varcharlpad前方补空格,保持最大长度是15位
这种方法是缺点是:
1 事先要知道,哪一列是数值型
2 15位定的要合适
3 补了太多的空格,难免对查询效率有些影响
select f1,f2,f3
from t1
where
f1+f2+f3>='111'
and f1+f2+f3<='211'
order by f1,f2,f3 如果为数值:
select f1,f2,f3
from t1
where
f1*100+f2*10+f3>=111
and f1*100+f2*10+f3<=211
order by f1,f2,f3
感觉有点象IP补0的查询方法,然后通过补零后再进行查询.不知道你是不是这个意思,参考:'61.11.11.2' 要变为 ‘061.011.011.002'
declare @a varchar(20),@b varchar(20)set @a ='61.11.11.2'
select top 30 id=identity(int,1,1) into # from syscolumnsselect @b=isnull(@b+'.','')+
right('00'+substring(@a,id,charindex('.',@a+'.',id+1)-id),3)
from #
where substring('.'+@a,id,1)='.'
select @b
drop table #
--result
/*
--------------------
061.011.011.002(所影响的行数为 1 行)
*/
来个投机取巧点的:
-------------------------------------------------------------------------
declare @str varchar(20)
set @str='61.11.11.2'select right('000'+ParseName(@str,4),3)+'.'
+right('000'+ParseName(@str,3),3)+'.'
+right('000'+ParseName(@str,2),3)+'.'
+right('000'+ParseName(@str,1),3)/*
---------------
061.011.011.002
*/
判断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','湖北省武汉市 长城宽带')
godeclare @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
GOdeclare @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' ,'广东省广州市 东兴小区长城宽带')
也就是有大区间包含小区间。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','湖北省武汉市 长城宽带')
godeclare @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.ipscoredrop table tb
/*
StartIP EndIP Area
--------------------------------------------------------------
220.113.53.0 220.113.53.255 广东省广州市 东兴小区长城宽带(1 行受影响)
*/
1 既有数值,也有字符的字段。不是只是一种类型的字段
2 值中长度是不确定的,有5位的(number(5)),也有10的(varchar(10))等等。不象ip是3*4那么固定。例子数据可能会是这样的:f1(数值型number(5)) f2(字符型varchar(10)) f3(数值型number(8)) f4(字符型varchar(20))
--------------------------------------------------------------------------------------
1 10aa 1.6 sss10bb
2 10aadddd 2.78 fvvvvvvvvvv
1 既有数值,也有字符的字段。不是只是一种类型的字段
2 值中长度是不确定的,有5位的(number(5)),也有10的(varchar(10))等等。不象ip是3*4那么固定。 例子数据可能会是这样的: f1(数值型number(5)) f2(字符型varchar(10)) f3(数值型number(8)) f4(字符型varchar(20))
--------------------------------------------------------------------------------------
1 10aa 1.6 sss10bb
2 10aadddd 2.78 fvvvvvvvvvv
insert #tb(f1,f2,f3)
select '1',1,1 union all
select '1',1,2 union all
select '1',1,3 union all
select '1',2,1 union all
select '1',2,2 union all
select '1',2,3 union all
select '1',3,1 union all
select '1',3,2 union all
select '1',3,3 union all
select '2',1,1 union all
select '2',1,2 union all
select '2',1,3 union all
select '2',2,1 union all
select '2',2,2 union all
select '2',2,3 union all
select '2',3,1 union all
select '2',3,2 union all
select '2',3,3 union all
select '10',1,1 union all
select '10',1,2 union all
select '10',1,3 union all
select '10',2,1 union all
select '10',2,2 union all
select '10',2,3 union all
select '1c',3,1 union all
select '1b',3,2 union all
select '1a',3,3declare @sql nvarchar(2000)
set @sql = N'select * from #tb order by '
set @sql = @sql + case (select count(f1) from #tb where isnumeric(f1) =1) when (select count(f1) from #tb) then 'cast(f1 as int)' else 'len(f1),f1' endprint @sql
EXECUTE sp_executesql @sqldrop table #tb
对你的需求越来越模糊了
f1(数值型number(5)) f2(字符型varchar(10)) f3(数值型number(8)) f4(字符型varchar(20))
--------------------------------------------------------------------------------------
1 10aa 1.6 sss10bb
2 10aadddd 2.78 fvvvvvvvvvv 根据上面的范围,我会形成两组条件:
wheresql1 = "000000000000001,10aa ,00000000000001.6,sss10bb "
wheresql2 = "000000000000002,10aadddd,0000000000002.78,fvvvvvvvvvv"SQL可以这么写:SELECT *
FROM t1
WHERE cast(前补零成15位(F1,15,'0') as varchar)+','+后补空格成30位(F2,30,' ')+','+cast(前补零成15位(F3,15,'0') as varchar)+','+后补空格成30位(F4,30,' ') >= wheresql1
and
cast(前补零成15位(F1,15,'0') as varchar)+','+后补空格成30位(F2,30,' ')+','+cast(前补零成15位(F3,15,'0') as varchar)+','+后补空格成30位(F4,30,' ') < wheresql2
ORDER BY F1,F2,F3,F4 谢谢各位,我要结贴了