提供的数据存在一些问题。目前尚不清楚主要出货数据是否在同一个表中。如果不是那么什么是连接列。另外，ORDER 是保留关键字，它不应该是列的名称。
事件顺序也有问题。唯一对其进行排序的列是“发货”列。幸运的是，它具有可以排序的值。存在一个问题，如果前一行目标位置不等于当前行源位置怎么办？
这是一个适用于问题中的数据的选项。请在尝试完成工作时考虑上述所有内容。
您的数据为：
WITH
tbl AS
(
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_A' "SHIPMENT", 'MFG_1' "SOURCE_LOCATION", 'WH_1' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_B' "SHIPMENT", 'WH_1' "SOURCE_LOCATION", 'WH_2' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_C' "SHIPMENT", 'WH_2' "SOURCE_LOCATION", 'WH_2' "DESTINATION_LOCATION", 'PROCESS' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_D' "SHIPMENT", 'WH_2' "SOURCE_LOCATION", 'BB_1' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_E' "SHIPMENT", 'BB_1' "SOURCE_LOCATION", 'BB_1' "DESTINATION_LOCATION", 'PROCESS' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_F' "SHIPMENT", 'BB_1' "SOURCE_LOCATION", 'PORT_A' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_G' "SHIPMENT", 'PORT_A' "SOURCE_LOCATION", 'PORT_B' "DESTINATION_LOCATION", 'VESSEL' "MODE" From Dual
)
执行类似的操作以获取一些附加列，稍后您将需要这些列作为嵌套查询来从中选择预期结果：
Select
t.*,
ROWNUM "RN",
CASE
WHEN SOURCE_LOCATION = SHIPMENT_SOURCE_LOCATION THEN 'START'
WHEN DESTINATION_LOCATION = SHIPMENT_DESTINATION_LOCAATION THEN 'END'
ELSE
FIRST_VALUE(DESTINATION_LOCATION) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between 1 Preceding And 1 Preceding)
END "PROGRESS"
From
tbl t
Order By
t.AN_ORDER, t.SHIPMENT
/* R e s u l t :
SHIPMENT_SOURCE_LOCATION SHIPMENT_DESTINATION_LOCAATION AN_ORDER SHIPMENT SOURCE_LOCATION DESTINATION_LOCATION MODE RN PROGRESS
------------------------ ------------------------------ -------- -------- --------------- -------------------- --------- ---------- --------
MFG_1 PORT_B ORDER_1 SHP_A MFG_1 WH_1 TRANSPORT 1 START
MFG_1 PORT_B ORDER_1 SHP_B WH_1 WH_2 TRANSPORT 2 WH_1
MFG_1 PORT_B ORDER_1 SHP_C WH_2 WH_2 PROCESS 3 WH_2
MFG_1 PORT_B ORDER_1 SHP_D WH_2 BB_1 TRANSPORT 4 WH_2
MFG_1 PORT_B ORDER_1 SHP_E BB_1 BB_1 PROCESS 5 BB_1
MFG_1 PORT_B ORDER_1 SHP_F BB_1 PORT_A TRANSPORT 6 BB_1
MFG_1 PORT_B ORDER_1 SHP_G PORT_A PORT_B VESSEL 7 END
*/
结果表明发货顺序也是您想要的“SEQUENCE”。它位于被选为伪列 ROWNUM 的 RN 列中。还有 PROGRESS 列，其中选择开始和结束，中间有前一行的目标位置。
这可用于检查您的条件（使用 case 表达式）并生成序列。
这是完整的代码和结果：
SELECT
AN_ORDER,
SHIPMENT,
Sum(CASE WHEN PROGRESS = SOURCE_LOCATION THEN 1
WHEN PROGRESS = 'END' THEN 1
ELSE 0
END) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between Unbounded Preceding And Current Row) + 1"SEQUENCE"
FROM
(
Select
t.*,
ROWNUM "RN",
CASE
WHEN SOURCE_LOCATION = SHIPMENT_SOURCE_LOCATION THEN 'START'
WHEN DESTINATION_LOCATION = SHIPMENT_DESTINATION_LOCAATION THEN 'END'
ELSE
FIRST_VALUE(DESTINATION_LOCATION) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between 1 Preceding And 1 Preceding)
END "PROGRESS"
From
tbl t
Order By
t.AN_ORDER, t.SHIPMENT
)
/* R e s u l t :
AN_ORDER SHIPMENT SEQUENCE
-------- -------- ----------
ORDER_1 SHP_A 1
ORDER_1 SHP_B 2
ORDER_1 SHP_C 3
ORDER_1 SHP_D 4
ORDER_1 SHP_E 5
ORDER_1 SHP_F 6
ORDER_1 SHP_G 7
*/
请考虑这是一个您应该考虑的选项，考虑到所有可能的问题。如果您的数据不处于完美状态，此代码将导致一些重复序列。
问候...
