blocks|key|1355124|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1355125|SELECT+*
FROM+(
+++SELECT+t.*,
++++++++++lead(+SEQ+)+over+(+partition+by+id+order+by+seq+)+next_seq,
++++++++++lead(+action+)+over+(+partition+by+id+order+by+seq+)+next_action
+++FROM+table1+T
)
WHERE+action+=+'SUSPEND'
++AND+next_action+<>+'CLEAR'|code-block|syntax|javascript|1355126|演示：http://sqlfiddle.com/#!4/5ea45/8|offset|length|1355127|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!4/5ea45/8^0|0|0|3|W|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Try:

<pre><code>SELECT *
FROM (
 SELECT t.*,
 lead( SEQ ) over ( partition by id order by seq ) next_seq,
 lead( action ) over ( partition by id order by seq ) next_action
 FROM table1 T
)
WHERE action = 'SUSPEND'
 AND next_action &lt;&gt; 'CLEAR'
</code></pre>

demo: <a href="http://sqlfiddle.com/#!4/5ea45/8" rel="noreferrer">http://sqlfiddle.com/#!4/5ea45/8</a>

blocks|key|1355163|text|我试图从kordirko的sqlfiddle，避免构建相同的模式.很抱歉偷东西。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1355164|请看我的尝试：http://sqlfiddle.com/#!4/5ea45/22|1355165|select+*
from+(select+id,
+++++++++++++seq,
+++++++++++++min(case+when+action+!=+'SUSPEND'+then+seq+end)+over+(partition+by+id+order+by+seq+rows+between+current+row+and+unbounded+following)+nxt
++++++from+(select+t.*,
+++++++++++++++++++lead(action)+over+(partition+by+id+order+by+seq)+nxt
++++++++++++from+table1+t)
++++++where+nxt+!=+'CLEAR'
++++++++and+action+!=+'CLEAR')
where+(seq+!=+nxt+or+nxt+is+null)|code-block|syntax|javascript|1355166|这样做的目的是：|1355167|只采取自己没有清除或“不清楚”的行动。|ordered-list-item|1355168|找出下一个动作，那就是“简历”，只看未来，尽量减少。|1355169|过滤结果以排除“当前行”|1355170|唯一要做的就是添加非常简单的操作名称，因为每个第一个操作都是“挂起”，如果不是空的话，则是“恢复”。我想你可以随心所欲地自己做:)|1355171|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!4/5ea45/22^0|4|8|0|7|X|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|H|3|I|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1D|8|@]|D|@]|E|$]]|$1|O|3|P|5|Q|7|1E|8|@]|D|@]|E|$]]|$1|R|3|S|5|Q|7|1F|8|@]|D|@]|E|$]]|$1|T|3|U|5|Q|7|1G|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|1H|8|@]|D|@]|E|$]]|$1|X|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|Y|$Z|$5|10|11|12|E|$13|14]]]]

I tried from sqlfiddle of kordirko, to avoid building the same schema... Sorry for stealing.

See my attempt here: <a href="http://sqlfiddle.com/#!4/5ea45/22" rel="nofollow">http://sqlfiddle.com/#!4/5ea45/22</a>

<pre><code>select *
from (select id,
 seq,
 min(case when action != 'SUSPEND' then seq end) over (partition by id order by seq rows between current row and unbounded following) nxt
 from (select t.*,
 lead(action) over (partition by id order by seq) nxt
 from table1 t)
 where nxt != 'CLEAR'
 and action != 'CLEAR')
where (seq != nxt or nxt is null)
</code></pre>

The idea is:

<ol>
<li>Take only the actions which are not cleared and not "clear" by themselves</li>
<li>Find out the next action which is "resume" by looking only into the future and getting minimum</li>
<li>Filter the result to exclude "current row"</li>
</ol>

The only thing left to do is to add names of actions which is quite simple, because every first action is "SUSPEND" and second, if it is not null, "RESUME". This I guess you can easily do by yourself as you wish :)

blocks|key|1077550|text|好的。现在我从科迪尔科偷小提琴，从西蒙偷概念，然后用我的方式烹饪它。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1077551|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;|code-block|syntax|javascript|1077552|因此，我从你的问题中了解到，它最初没有得到正确的解释。但是第二个预期的结果起了作用。(我可能还是错了)|1077553|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';|1077554|输出：|style|BOLD|1077555|12345+++2+++SUSPEND+5+++RESUME
12345+++6+++SUSPEND+++++|1077556|我所做的是分配一个标志来检查设置为被忽略的连续操作，如果它们有一个名为CLEAR的操作或一个名为CLEAR的下一个操作。然后，我使用kordirko的LEAD函数片段来完成这项工作，并在需要的操作上设置了过滤器。|CODE|1077557|见小提琴+这里|1077558|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/2568341/kordirko|1|https://stackoverflow.com/users/1990451/simon|2|http://sqlfiddle.com/#!4/596b0/1/0^0|7|4|0|H|2|1|0|0|0|0|0|3|0|0|Z|5|1C|5|23|4|0|0|4|5|2|2|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@$A|1A|B|1B|1|1C]|$A|1D|B|1E|1|1F]]|C|$]]|$1|D|3|E|5|F|7|1G|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1H|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|1I|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|1J|8|@$A|1K|B|1L|O|P]]|9|@]|C|$]]|$1|Q|3|R|5|F|7|1M|8|@]|9|@]|C|$G|H]]|$1|S|3|T|5|6|7|1N|8|@$A|1O|B|1P|O|U]|$A|1Q|B|1R|O|U]|$A|1S|B|1T|O|U]]|9|@]|C|$]]|$1|V|3|W|5|6|7|1U|8|@$A|1V|B|1W|O|P]]|9|@$A|1X|B|1Y|1|1Z]]|C|$]]|$1|X|3|-4|5|6|7|20|8|@]|9|@]|C|$]]]|Y|$Z|$5|10|11|12|C|$13|14]]|15|$5|10|11|12|C|$13|16]]|17|$5|10|11|12|C|$13|18]]]]

OK. Now I steal fiddle from <a href="https://stackoverflow.com/users/2568341/kordirko">kordirko</a> and concept from <a href="https://stackoverflow.com/users/1990451/simon">simon</a> and cook it in my way.

<pre><code>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;
</code></pre>

So what I understand from your problem is that its is not explained correctly initially. But the second expected result does the trick. (I might be still wrong)

<pre><code>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';
</code></pre>

OUTPUT:

<pre><code>12345 2 SUSPEND 5 RESUME
12345 6 SUSPEND 
</code></pre>

What I did was assigning a flag to check consecutive actions set to be ignored if they have an ACTION called <code>CLEAR</code> or a NEXT ACTION called <code>CLEAR</code>. Then I used kordirko's snippet of <code>LEAD</code> function to do the job with a filter on needed ACTION.

See the fiddle <a href="http://sqlfiddle.com/#!4/596b0/1/0" rel="nofollow noreferrer">here</a>

blocks|key|1355248|text|这种修改后的kordirkos解决方案应该可以工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1355249|SELECT+*
FROM+(
+++SELECT+t.*,
++++++++++lead(+SEQ+)+over+(+partition+by+id+order+by+seq+)+next_seq,
++++++++++lead(+action+)+over+(+partition+by+id+order+by+seq+)+next_action
+++FROM+table1+T
)
WHERE+action+in+('SUSPEND',+'RESUME')
++AND+(next_action+<>+'CLEAR'+or+next_action+is+null)|code-block|syntax|javascript|1355250|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This modified version of kordirkos solution should work:

<pre><code>SELECT *
FROM (
 SELECT t.*,
 lead( SEQ ) over ( partition by id order by seq ) next_seq,
 lead( action ) over ( partition by id order by seq ) next_action
 FROM table1 T
)
WHERE action in ('SUSPEND', 'RESUME')
 AND (next_action &lt;&gt; 'CLEAR' or next_action is null)
</code></pre>

I have a table of data detail the action sequence of a row on a parent table where column ID is that foreign key. Column SEQ is the order in which those actions took place and ACTION is what took place.

<pre><code>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
</code></pre>

I am trying to present the data in such a way that I can identify SUSPEND and RESUME actions that were not cleared. In this scenario my result would look like this;

<pre><code>12345.00 7 SUSPEND 8 RESUME
12345.00 9 SUSPEND
</code></pre>

This is the case because;

<blockquote>
 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.
</blockquote>

The ACTION column can have other actions in the sequence so I have removed those for clarity. 

An action is cleared if it will be succeeded by a CLEAR.

Sorry if this is confusing. I cannot change the schema!

I have tried to simplify the question;

<pre><code>ID SEQ ACTION
12345.00 2 SUSPEND
12345.00 3 RESUME
12345.00 4 CLEAR
12345.00 5 RESUME
12345.00 6 SUSPEND
</code></pre>

The result should look like this;

<pre><code>12345.00 2 SUSPEND 5 RESUME
12345.00 6 SUSPEND
</code></pre>

I have tried a couple of approaches but I just cannot figure how to stop the RESUME at number 3 being included.

Oracle Sequential actions

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个数据明细表，即父表中一行的操作序列，其中列ID是外键。SEQ栏是这些行动发生的顺序，行动是发生的顺序。ID          SEQ ACTION12345.00    2   SUSPEND12345.00    3   CLEAR12345.00    4   SUSPEND12345.00    6   ...

问Oracle顺序操作
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle顺序操作EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle顺序操作
EN