oracle中的between使用

最新推荐文章于 2025-10-20 19:15:57 发布

原创最新推荐文章于 2025-10-20 19:15:57 发布 · 10w+ 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

本文通过实例演示了Oracle SQL中BETWEEN操作符的具体用法，特别是针对number和date类型的查询，强调了BETWEEN操作符实现的是闭区间查询。

oracle between

测试环境：

Oracle Database 10g Enterprise Edition/SQL*Plus 10.2.0.4.

遇到一个要使用between的小case，专门又看了下，结果发现了小宇宙

先建个表吧：

create table test_s
(
id number,
col2 date,
col3 varchar(10)
);

本人渣渣，怎么简单怎么来哦

这里只谈了下 number类型和date类型

插点数据

insert into test_s values(1,'20160920 23:00:00','2015-03-01');
insert into test_s values(2,'20160920 23:00:01','2015-03-02');
insert into test_s values(3,'20160920 23:00:02','2015-03-04');
insert into test_s values(4,'20160920 22:00:00','2015-03-04');
insert into test_s values(5,'20160920 22:02:00','2015-03-05');
insert into test_s values(11,'20160921 23:00:00','2015-04-02');
insert into test_s values(12,'20160921 23:00:01','2015-04-04');
insert into test_s values(13,'20160921 23:00:02','2015-04-05');
insert into test_s values(15,'20160921 22:02:00','2015-04-06');
insert into test_s values(14,'20160921 22:00:00','2015-04-07');

select * from test_s;

看看between的效果

select * from test_s where id between 2 and 12 order by 1;

1）.也就是说，number类型左右区间都是闭区间。

select * from
test_s where col2 between '20160920 23:00:00' and '20160921 23:00:00' order by 2;

select * from
test_s where col2 between '20160920 23:00:00' and '20160921 23:00:01' order by 2;

看来是我眼花了，这个也是全闭区间。

select * from test_s where col3 between '2015-03-01' and '2015-04-02' order by 3;

。。。。。。。。。。。