oracle 循环访问记录并根据条件替换多个记录中的属性

0pizxfdo 于 2022-11-03 发布在 Oracle

关注(0)|答案(1)|浏览(124)

我尝试清理以下数据。
| 载体|自|结束|启动|替换|
| - -|- -|- -|- -|- -|
| 2个|A级|B| A级||
| 2个|B| C类|A级||
| 2个|C类|D级|A级||
| 2个|D级|我的天|A级||
| 2个|我的天|F级|E级|一百二十三|
| 2个|G级|高|E级|一百二十三|
| 2个|一个|J型|E级|一百二十三|
| 2个|W型|X轴|W型||
| 2个|X轴|Y型|W型||
| 2个|Y型|Z轴|W型||
| 三个|第一季度|第二季度|W型||
| 三个|第二季度|第三季度|W型||
我的目标是，只要存在具有相同车辆且replacement不为NULL的记录，并且存在replacement为NULL的记录（对于同一车辆）并且具有替换NULL的记录的TO属性等于具有替换非NULL的记录的FROM属性，它应该替换所有记录中的START属性，在这些记录中，替换不是空的，并且用上面匹配的START属性替换相同的车辆。
因此，结果应如下所示：
| 载体|自|结束|启动|替换|
| - -|- -|- -|- -|- -|
| 2个|A级|B| A级||
| 2个|B| C类|A级||
| 2个|C类|D级|A级||
| 2个|D级|E级|A级||
| 2个|E级|F级|一个| 一百二十三|
| 2个|G级|高|一个| 一百二十三|
| 2个|一个|J型|一个| 一百二十三|
| 2个|W型|X轴|W型||
| 2个|X轴|Y型|W型||
| 2个|Y型|Z轴|W型||
| 三个|第一季度|第二季度|W型||
| 三个|第二季度|第三季度|W型||
请注意，还有车辆= 2的其他条目不受影响（W-Z）
我已经在纯SQL中尝试了很多带有超前和滞后的方法，所以我可以替换第一行（从E到F），但我无法替换所有必要的行，因为没有任何标准可供使用。所以我想没有办法使用PL/SQL并将其过程化，但我不知道具体如何解决这个问题。我可以做一些PL/SQL，但我不是这方面的Maven。
这是上面那套table要用的，如果您能帮忙，我将不胜感激。

WITH a AS (
SELECT 2 "vehicle", 'A' "FROM", 'B' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'B' "FROM", 'C' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'C' "FROM", 'D' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'D' "FROM", 'E' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'E' "FROM", 'F' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'G' "FROM", 'H' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'I' "FROM", 'J' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'W' "FROM", 'X' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'X' "FROM", 'Y' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'Y' "FROM", 'Z' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q1' "FROM", 'Q2' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q2' "FROM", 'Q3' "TO", 'W' "START", NULL "replacement" FROM dual
)
SELECT * FROM a;

oracle

来源：https://stackoverflow.com/questions/73981582/iterate-through-records-and-replace-attributes-in-multiple-records-based-on-cond

1条答案

按热度按时间

vsnjm48y1#

您可以使用match_recognition来实现以下目的：
为了使它更容易理解，我将所需的结果添加为NEW_START列。
数据库小键盘：https://dbfiddle.uk/PgCeARDJ

WITH a AS (
SELECT 2 "vehicle", 'A' "FROM", 'B' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'B' "FROM", 'C' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'C' "FROM", 'D' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'D' "FROM", 'E' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'E' "FROM", 'F' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'G' "FROM", 'H' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'I' "FROM", 'J' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'W' "FROM", 'X' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'X' "FROM", 'Y' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'Y' "FROM", 'Z' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q1' "FROM", 'Q2' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q2' "FROM", 'Q3' "TO", 'W' "START", NULL "replacement" FROM dual
)
SELECT 
   "vehicle"
  ,"FROM"
  ,"TO"
  ,"START"
  ,"replacement"
  ,new_start,mno,cls
FROM a
match_recognize(
  partition by "vehicle"
  order by "FROM"
  MEASURES  
     A."START" as NEW_START
    ,MATCH_NUMBER() AS mno
    ,CLASSIFIER() AS cls
  ALL ROWS PER MATCH
  pattern (A (B C*)?)
  DEFINE
    B as (B."FROM" = prev(B."TO") and B."replacement" is not null and prev(B."replacement") is null),
    C as (C."replacement" is not null)
)
order by 1,2
;

赞(0）回复(0）举报 2022-11-03

我来回答

oracle 循环访问记录并根据条件替换多个记录中的属性

1条答案

相关问题

热门标签

最新问答