oracle如何查询当天的数据!

2011-03-23 11:25:31 7.8k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Zemax物理光学实战：3分钟搞定高斯光束传播模拟（附菲涅尔局部分析技巧）本文详细介绍了在Zemax中进行高斯光束传播模拟与菲涅尔局部分析的实战技巧。通过3分钟快速设置指南和参数解析，帮助光学工程师准确模拟激光光束特性，避免常见误区。特别分享了菲涅尔局部分析的关键技巧和传播参数优化策略，提升光学系统设计的精度与效率。阅读详情

在test表里有一个dadate日期字段里面的格式如下

id dadate
1 2011-3-23 10:23:45
2 2011-2-21 12:34:21
3 2011-3-12 6:23:12

我想按dadate 查询当天的记录如何查询？望高手告知，谢谢

...全文

中国传媒大学计算机考研初复试经验贴中传的计算机专业考研的话主要分为两大学院：计网学院和国重，大部分专业在计网学院。阅读详情

yangzong20

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

xuyazhou 2011-06-06

打赏
举报

[Quote=引用 7 楼 yinan9 的回复:]
SQL code
select * from test where trunc(dadate)=trunc(sysdate)
[/Quote]
对的

xuyazhou 2011-06-06

打赏
举报

good

show_love 2011-03-23

打赏
举报

select * from test t where trunc(sysdate)=trunc(datate);

liujt17 2011-03-23

打赏
举报

select * from test where trunc(dadate)=trunc(sysdate)

超超王贱贱 2011-03-23

打赏
举报



select * from test where trunc(dadate)=trunc(sysdate)

yinan9 2011-03-23

打赏
举报

select * from test where trunc(dadate)=trunc(sysdate)

changhe325 2011-03-23

打赏
举报

楼主看下有关oracle时间的函数吧。

newdiscover 2011-03-23

打赏
举报

[Quote=引用 4 楼 luoyoumou 的回复:]
SQL code
-- 查询一表中昨天生成的数据
-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);

-----------------------------------------------------------------------……
[/Quote]

佩服！

luoyoumou 2011-03-23

打赏
举报

-- 查询一表中昨天生成的数据

-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);



---------------------------------------------------------------------------------------------------------------------

-- 方法一：用to_char()函数



hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd');



  COUNT(*)

----------

     82119



已用时间:  00: 00: 17.18



执行计划

----------------------------------------------------------

Plan hash value: 3731074549



------------------------------------------------------------------------------------------------

| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11773  (16)| 00:02:22 |

|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11773  (16)| 00:02:22 |

------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy

              -mm-dd'))





统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



---------------------------------------------------------------------------------------------------------------------

-- 方法二：用trunc()函数



hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate-1);



  COUNT(*)

----------

     82119



已用时间:  00: 00: 16.32



执行计划

----------------------------------------------------------

Plan hash value: 3731074549



------------------------------------------------------------------------------------------------

| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 12580  (22)| 00:02:31 |

|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 12580  (22)| 00:02:31 |

------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



---------------------------------------------------------------------------------------------------------------------

-- 方法三：不用函数

hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);



  COUNT(*)

----------

     82119



已用时间:  00: 00: 00.43



执行计划

----------------------------------------------------------

Plan hash value: 2668176725



---------------------------------------------------------------------------------------------

| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                        |     1 |     8 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |

|*  2 |   FILTER           |                        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))

   3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        221  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



---------------------------------------------------------------------------------------------------------------------





-- 由上三种方法可见：

-- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；

-- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；

-- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；





-- 请问：哪种方法好呢？

Megan2008 2011-03-23

打赏
举报

select * from test where dadate>=trunc(sysdate) and dadate<trunc(sysdate)+1

UPC子夜 2011-03-23

打赏
举报

select * from test where to_char(dadate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')

oracle如何查询当天的数据!

相关推荐