oracle制定顺序,Oracle顺序操作

好.现在我从

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", "SEQ", "ACTION" )

VALUES ( 12345.00, 3, 'RESUME' )

INTO TABLE1 ( "ID", "SEQ", "ACTION" )

VALUES ( 12345.00, 4, 'CLEAR' )

INTO TABLE1 ( "ID", "SEQ", "ACTION" )

VALUES ( 12345.00, 5, 'RESUME' )

INTO TABLE1 ( "ID", "SEQ", "ACTION" )

VALUES ( 12345.00, 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,

T.SEQ,

T.ACTION,

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