我有一个数据表详细说明父表的行的动作序列,其中列ID是该外键.列SEQ是这些动作发生的顺序,ACTION是发生的动作.
ID SEQ ACTION 12345.00 2 SUSPEND 12345.00 3 CLEAR 12345.00 4 SUSPEND 12345.00 6 CLEAR 12345.00 7 SUSPEND 12345.00 8 RESUME 12345.00 9 SUSPEND 12345.00 10 RESUME 12345.00 11 CLEAR
我试图以这样的方式呈现数据,以便我可以识别未清除的SUSPEND和RESUME操作.在这种情况下,我的结果将如下所示;
12345.00 7 SUSPEND 8 RESUME 12345.00 9 SUSPEND
情况就是这样;
SUSPEND action 2 was removed by CLEAR action 3.
SUSPEND action 4 was removed by CLEAR action 6.
RESUME action 8 was removed by CLEAR action 9.
ACTION列可以在序列中包含其他操作,因此为了清楚起见,我已删除了这些操作.
如果CLEAR将继续执行操作,则清除该操作.
对不起,如果这令人困惑.我无法改变架构！
我试图简化这个问题;
ID SEQ ACTION 12345.00 2 SUSPEND 12345.00 3 RESUME 12345.00 4 CLEAR 12345.00 5 RESUME 12345.00 6 SUSPEND
结果应该是这样的;
12345.00 2 SUSPEND 5 RESUME 12345.00 6 SUSPEND
我已经尝试了几种方法,但我无法想象如何停止包含3号的RESUME.
好.现在我从
kordirko偷取小提琴,从
simon开始概念,然后以我的方式烹饪.
CREATE TABLE TABLE1 ( "ID" INT,"SEQ" INT,"ACTION" VARCHAR2 ( 7 ) ); INSERT ALL INTO TABLE1 ( "ID","SEQ","ACTION" ) VALUES ( 12345.00,2,'SUSPEND' ) INTO TABLE1 ( "ID",3,'RESUME' ) INTO TABLE1 ( "ID",4,'CLEAR' ) INTO TABLE1 ( "ID",5,6,'SUSPEND' ) SELECT * FROM DUAL;
所以我从你的问题中理解的是,最初没有正确解释它.但第二个预期的结果就是诀窍. (我可能还是错了)
WITH AFTER_CHECK
AS (SELECT T.ID,T.SEQ,T.ACTION,CASE
WHEN ACTION = 'CLEAR'
THEN
1
WHEN LEAD ( ACTION ) OVER (PARTITION BY ID ORDER BY SEQ) =
'CLEAR'
THEN
1
ELSE
0
END
AS IGNORE_CURRENT
FROM TABLE1 T)
SELECT *
FROM (SELECT T.ID,LEAD ( SEQ ) OVER (PARTITION BY ID ORDER BY SEQ) NEXT_SEQ,LEAD ( ACTION ) OVER (PARTITION BY ID ORDER BY SEQ)
NEXT_ACTION
FROM AFTER_CHECK T
WHERE IGNORE_CURRENT = 0)
WHERE ACTION = 'SUSPEND';
OUTPUT：
12345 2 SUSPEND 5 RESUME 12345 6 SUSPEND
我所做的是分配一个标志来检查设置为忽略的连续动作,如果它们有一个名为CLEAR的ACTION或一个名为CLEAR的NEXT ACTION.然后我使用kordirko的LEAD函数片段来完成所需的ACTION过滤器的工作.
见小提琴here
