时间戳

首页 > 解决方案 > oracle中的日期元素

sql - oracle中的日期元素

问题描述

我想在我的 Sql 中添加日期条件，但不知道怎么做。下面的SQL...

    select 
      dsp_sku, sum(INVN_ADJMT_QTY) qty, 
      decode(pt.tran_type||pt.tran_code||pt.actn_code, '60500','Total SKU inventory','60501','Total case inventory (allocatable)','60502','Total carton inventory','60503','Total active inventory', '60504','Total transitional inventory (allocatable)','60506','Total case-pick inventory','60511','Unallocatable case inventory','60514','Unallocatable transitional inventory', '60597','Total SKU inventory - Allocatable','60598','Total SKU inventory - UnAllocatable','605TXX','Transitional inventory by type (XX=trans. type)', '605T10','Transitional inventory by type (10)','605T20','Transitional inventory by type (20)','605T30','Transitional inventory by type (30)', '605T40','Transitional inventory by type (40)','605T0','Transitional inventory by type (0)','605T99','Transitional inventory by type (99)', '605LPN','Case Inventory by lock code (PN)','605LRC','Case Inventory by lock code (RC)','605LQA','Case Inventory by lock code (QA)','605LQH','Case Inventory by lock code (QH)', '605LEX','Case Inventory by lock code (EX)','605LRW','Case Inventory by lock code (RW)','605LDM','Case Inventory by lock code (DM)','605LWD','Case Inventory by lock code (WD)', '605LQU','Case Inventory by lock code (QU)','605LCH','Case Inventory by lock code (CH)','605LSD','Case Inventory by lock code (SD)','605LBL','Case Inventory by lock code (BL)', '605LCC','Case Inventory by lock code (CC)','605LML','Case Inventory by lock code (ML)','605LXX','Case Inventory by lock code (XX=lock code)' ) STATUS, 
      pt.tran_type||pt.tran_code||pt.actn_code code_blend
    from PIX_TRAN pt
      inner join item_master im on
        im.sku_id = pt.sku_id 
    where
      tran_type = '605' 
    --and tran_code = '01'
      --and actn_code
      and whse = 'PH3' 
    --and trunc(pt.create_date_time) like trunc(sysdate) - 1
      and (
(im.dsp_sku ='85865') or
(im.dsp_sku ='86111') or
(im.dsp_sku ='173016') or
(im.dsp_sku ='176630') or
(im.dsp_sku ='72204064')
)

    group by 
      dsp_sku, tran_type||tran_code||actn_code
    order by 
      code_blend, dsp_sku

在查看 SKU 的部分中，

 and (
(im.dsp_sku ='85865') or
(im.dsp_sku ='86111') or
(im.dsp_sku ='173016') or
(im.dsp_sku ='176630') or
(im.dsp_sku ='72204064')
)

我想给它添加一个日期元素。查询的目的是查看某天建筑物中是否有库存，所以我想说(im.dsp_sku = '85864' (then how ever you would have it check for the specific day)的是我需要在某个日期检查每个 SKU，所以我不能每个都进行一次大规模搜索SKU 有自己的日期需要检查，我有大约 11k SKU 需要检查。感谢您的任何见解。

标签： sqloracle

解决方案

我建议您不要TRUNC在日期列上应用，因为它会导致日期列上的现有索引（如果有）未被使用，从而降低其性能。

TRUNC将您的陈述重写为

pt.create_date_time  >= DATE '2019-05-05' AND 
pt.create_date_time  <  DATE '2019-05-05' + 1

如果您想以所需格式传递日期值，请使用TO_DATE，但仅在右侧。

pt.create_date_time  >= TO_DATE( '05/05/2019','dd/mm/yyyy') AND 
pt.create_date_time  <  TO_DATE( '05/05/2019','dd/mm/yyyy') + 1 
                                          --or mm/dd/yyyy

推荐阅读