Oracle SQL日期过滤不生效：ship_by_date筛选条件的正确写法咨询

失效原因 #

• 你直接用字符串'01/01/2021'和DATE类型的oh.ship_by_date比较，依赖Oracle的隐式类型转换，转换规则和会话的NLS_DATE_FORMAT参数有关，不符合预期就会导致过滤逻辑失效
• 若你之前把过滤条件加在了外层q1的查询上，q1里的ship_by_date已经被TO_CHAR转成了字符串类型，用DATE逻辑去比较也会失效

正确实现方案 #

优先把过滤条件加在三个子查询的表头表查询语句中，提前过滤数据可以大幅提升查询性能，过滤条件显式指定日期转换格式，避免隐式转换问题：

WHERE oh.ship_by_date BETWEEN TO_DATE('01/01/2021', 'DD/MM/YYYY') AND CURRENT_DATE

修改后完整代码 #

select  
  q1.order_id, q1.status,  q1.ship_by_date, q1.shipped_date,
  count(q1.order_id) lines, sum(q1.qty_ordered) qty, 
  q1.user_def_num_4, q1.user_def_note_2, q1.name, q1.order_type, q1.seller_name, 
  sum(q1.qty_shipped), 
  q1.work_group, q1.creation_Date
from
(
  SELECT 
    OH.ORDER_ID, OH.STATUS, TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
    TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
    oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
    oh.seller_name, oh.creation_date, oh.work_group
  FROM V_ORDER_HEADER Oh
  left JOIN v_order_line ol on oh.order_id = ol.order_id
  WHERE oh.ship_by_date BETWEEN TO_DATE('01/01/2021', 'DD/MM/YYYY') AND CURRENT_DATE
  union all
  (
    SELECT 
      OH.ORDER_ID, OH.STATUS,  TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
      TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
      oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
      oh.seller_name, oh.creation_date, oh.work_group
    FROM V_ORDER_HEADER_archive Oh
    left JOIN v_order_line_archive ol on oh.order_id = ol.order_id
    WHERE oh.ship_by_date BETWEEN TO_DATE('01/01/2021', 'DD/MM/YYYY') AND CURRENT_DATE
    union
    SELECT 
      OH.ORDER_ID, OH.STATUS, TO_CHAR( OH.SHIP_BY_DATE, 'YYYY-MM-DD')  ship_by_date,
      TO_CHAR(OH.SHIPPED_DATE,'YYYY-MM-DD') shipped_date, ol.QTY_ORDERED, 
      oh.user_def_num_4, oh.user_def_note_2, oh.name, oh.order_type, ol.qty_shipped,
      oh.seller_name, oh.creation_date, oh.work_group
    FROM V_ORDER_HEADER_archive_2 Oh
    left JOIN v_order_line_archive ol on oh.order_id = ol.order_id
    WHERE oh.ship_by_date BETWEEN TO_DATE('01/01/2021', 'DD/MM/YYYY') AND CURRENT_DATE
  )
) q1
group by 
  q1.order_id, q1.status, q1.ship_by_date,  q1.shipped_date, 
  q1.NAME, q1.order_type, q1.user_def_num_4, q1.user_def_note_2, q1.seller_name,
  q1.work_group, q1.creation_Date

内容的提问来源于stack exchange，提问作者CC - SQL Beginner