如何在SQL中减去两列的日期

Question

请查看这个编辑过的表格。

你可以将order_header_key视为订单号。

我想要获取当前状态为3且之前状态为2，并且该订单的状态日期（status3）-状态日期（2）<= 3天的订单号列表。

在以下表格中，对于订单号1，“状态3日期” -“状态2日期” = 10月20日-10月19日，小于3天->因此是有效的订单。

但是对于订单号3，“状态3日期” -“状态2日期” = 10月30日-10月24日，超过3天，因此是无效的订单。

订单号2无效，因为状态为3和1，缺少2。

- Nagaraj B

Answer 1

假设一个订单在相同的订单号和状态组合下不能有多个条目，那么您可以连接两个子查询：

SELECT s3.order_no
FROM   (SELECT *
        FROM   orders
        WHERE  status = 3) s3
JOIN   (SELECT *
        FROM   orders
        WHERE  status = 2) s2 ON s3.order_no = s2.order_no AND 
                                 s3.status_date - s3.status_date <= 3

- Mureinik

---

嘿，感谢您的回复，我刚刚编辑了问题...订单状态将存储在数据库中，并针对日期进行记录，就像... - Nagaraj B

嘿，感谢您的回复，我刚刚编辑了问题...订单状态将存储在数据库中，并针对日期进行记录，就像... - Nagaraj B

嘿，感谢您的回复，我刚刚编辑了问题...订单状态将存储在数据库中，并与日期对应，例如订单编号1的状态为--18日创建订单，然后19日发货，20日交付。 - Nagaraj B

Answer 2

使用 lag()：

select o.*
from (select o.*,
             lag(o.status_date) over (partition by o.order_no order by o.status_date ) as prev_sd,
             lag(o.status) over (partition by o.order_no order by o.status_date) as prev_status
      from orders o
     ) o
where prev_status = 2 and status = 3 and
      (status_date - prev_sd) <= 3;

- Gordon Linoff

---

在 lag() 的分析子句中，order by 是强制性的（因为明显的逻辑原因和语法规则所要求）；如果没有它，查询将无法编译。 - user5683823

@mathguy...谢谢。我刚才不小心漏掉了它。 - Gordon Linoff

一个小问题，如果我想要获取两个其他状态之间的日期差异（比如状态3和1），我该怎么做？ - Nagaraj B

因为在滞后排序中只使用了order by，所以它只获取前一个记录，如何选择符合特定条件的记录？在我的情况下，我想根据它们的状态获取记录。 - Nagaraj B

@NagarajB...这是一个非常不同的问题，应该单独提出来问。这个问题很明显是关于状态2后面跟着状态3的记录。你需要非常清楚地说明状态是否可以重复，并包含适当的样本数据。 - Gordon Linoff

我之前不知道这个 lag 函数。 - Nagaraj B

Answer 3

分析函数（在这种情况下使用的是lag()）允许您避免连接和/或子查询，并且通常会更快。

with
--   begin test data; not part of the solution
     orders ( order_no, status, status_date ) as (
       select 1, 1, to_date('18-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 2, to_date('19-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 3, to_date('20-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 1, to_date('20-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 3, to_date('23-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 2, to_date('24-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 1, to_date('30-OCT-16', 'DD-MON-YY')from dual       
     ),
--   end test data; solution is the word "with" from above, plus the query below     
     prep ( order_no, status, status_date, prev_status, prev_status_date) as (
       select order_no, status, status_date,
              lag(status) over (partition by order_no order by status_date),
              lag(status_date) over (partition by order_no order by status_date)
       from   orders
     )
select order_no
from   prep
where  status = 3 and prev_status = 2 and prev_status_date - status_date <= 3
;


ORDER_NO
--------
       1

- user5683823