Oracle查询最近一年数据以及两个日期间隔天数-蒲公英云

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

一、查询之前需要格式化日期，用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

结果如下：
在这里插入图片描述
三、关联查询
查询统计行项目数，比如只查询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')