17,086
社区成员
发帖
与我相关
我的任务
分享
--创建表
create table t
(
a number,
b varchar(10)
)
--插入数据
insert into t value
(select 1 a, '10' b
from dual
union
select 1 a, '13' b
from dual
union
select 0 a, 'aa' b from dual)
--查询表数据
select * from (
select a,b from t where trim(translate(b,'0123456789',' ')) is null
)f where to_number(b)>5 and to_number(b)<20 ;
select a,b from t1 where t1.b>='5' and t1.b<='30';
with t1 as(
select 1 a, '10' b from dual
union
select 1 a, '13' b from dual
union
select 0 a, 'aa' b from dual
)
select a,b from t1 where t1.b>='10' and t1.b<='30';
select * from tb
where a=1
and to_number(b)>=10
and to_number(b)<=20
select *
from table
where
a=1
and
b>='10' and b<='20'
select *
from table
where
a=1
and
b>='10' and b<='20'
with t1 as(
select 1 a, '10' b from dual
union
select 1 a, '13' b from dual
union
select 0 a, 'aa' b from dual
)
select * from t1 where
a = 1 and b between '10' and '20'
with t1 as(
select 1 a, '10' b from dual
union
select 1 a, '13' b from dual
union
select 0 a, 'aa' b from dual
)
select * from (
select a,b from t1 where trim(translate(b,'0123456789',' ')) is null
)f where to_number(b)>5 and to_number(b)<20
with t1 as(
select 1 a, '10' b from dual
union
select 1 a, '13' b from dual
union
select 0 a, 'aa' b from dual
)
select a,b from t1 where t1.b>='10' and t1.b<='30';
SQL> ed
已写入 file afiedt.buf
1 CREATE TABLE t(
2 a NUMBER,
3 v varchar(5)
4* )
SQL> /
表已创建。
SQL> ed
已写入 file afiedt.buf
1* INSERT INTO t VALUES(1,'10')
SQL> /
已创建 1 行。
SQL> ed
已写入 file afiedt.buf
1* INSERT INTO t VALUES(1,'13')
SQL> /
已创建 1 行。
SQL> ed
已写入 file afiedt.buf
1* INSERT INTO t VALUES(0,'aa')
SQL> /
已创建 1 行。
SQL> select * from t;
A V
---------- -----
1 10
1 13
0 aa
SQL> ed
已写入 file afiedt.buf
1 select * from t
2* WHERE a = 1 AND v <=20 AND v >=10
SQL> /
A V
---------- -----
1 10
1 13
SQL>
select * from tb
where a=1
and to_number(b)>=10
and to_number(b)<=20
字段a(number) 字段b(varchar)
1 10
1 13
0 aa