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
使用 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;
lag()
的分析子句中,order by
是强制性的(因为明显的逻辑原因和语法规则所要求);如果没有它,查询将无法编译。 - user5683823分析函数(在这种情况下使用的是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