Oracle查询最近一年数据以及两个日期间隔天数

左手的ㄟ右手 2023-02-11 08:56 14阅读 0赞

一、查询之前需要格式化日期，用to\_char(日期，格式)函数进行转换

SELECT D.ORDER_NUM ,
           D.EMP_NAME
    FROM DAT_DOCUMENT d
    WHERE D.FORM_NAME ='Form_SD01'
    AND TO_CHAR(D.CREATE_TIME,'yyyy-MM-dd') BETWEEN '2019-01-01' AND '2020-03-17';

二、查询两个日期直接间隔天数：

SELECT TO_char(d.CREATE_TIME,'yyyy-MM-dd'),
           TO_char(d.UPDATE_TIME,'yyyy-MM-dd'),   
           trunc(d.UPDATE_TIME) - trunc(d.CREATE_TIME) as days
    FROM dat_document d

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]  
三、关联查询  
查询统计行项目数，比如只查询7月份到8月份数据

select extractvalue(d.document_data, '/root/orderNum') as "单号",
           count(1) as "行项目数",
           extractvalue(d.document_data, '/root/amount') as "总金额",
           extractvalue(d.document_data, '/root/text_Purchaser_display') as "采购员"
      from dat_document d, dat_table_row t
     where t.document_id = d.document_id
       and to_char(d.create_time,'yyyy-MM-dd') between '2020-07-01' and '2020-08-31'
     -- and d.document_id = 'docid:d551c0cf-d328-43be-a9ae-e1c3ee8ecf0e'
       and t.table_id = 'table2'
     group by extractvalue(d.document_data, '/root/orderNum'),
              extractvalue(d.document_data, '/root/amount'),
              extractvalue(d.document_data, '/root/text_Purchaser_display')

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200522180149529.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200910105253192.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70#pic_center