以oracle中所有可能的组合将行工作流数据转换为列工作流
以oracle中所有可能的组合将行工作流数据转换为列工作流
提问于 2017-10-31 10:44:52
我的系统中有一个工作流过程，它的数据格式如下。
From To
1 2
1 3
2 4
3 5
4 5
5 6
6 7
6 8
7 9
8 9我需要把这个转换成下面的格式。
S1 S2 S3 S4 S5 S6 S7
1 2 4 5 6 7 9
1 3 5 6 7 9
1 3 5 6 8 9
1 2 4 5 6 8 9或
S1 S2 S3 S4 S5 S6 S7
1 2 4 5 6 7 9
1 3 5 6 7 9
6 8 9
6 8 9或任何其他有用的格式
输出表示工作流过程中可以遵循的可能路径数。你可以假设我有信息，这是我的第一阶段和最后阶段。在这种情况下，您可以假设1 = first阶段和9 = final。因此，一旦用户到达Stage 1，他就可以选择使用Stage 2还是Stage 3
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-10-31 11:39:55
您需要分层查询，如下所示：
select path
from (select stage_to end,
sys_connect_by_path(stage_from, ' => ') || ' => ' || stage_to path
from test
start with stage_from = 1
connect by stage_from = prior stage_to)
where end = 9;如果一列中的输出是可以接受的，请使用sys_connect_by_path，但是如果需要未知数量的输出列，则可以尝试回答以下问题：Dynamic pivot in oracle sql。
测试数据和输出：
with test (stage_from, stage_to) as (
select 1, 2 from dual union all
select 1, 3 from dual union all
select 2, 4 from dual union all
select 3, 5 from dual union all
select 4, 5 from dual union all
select 5, 6 from dual union all
select 6, 7 from dual union all
select 6, 8 from dual union all
select 7, 9 from dual union all
select 8, 9 from dual)
select path
from (select stage_to end,
sys_connect_by_path(stage_from, ' => ') || ' => ' || stage_to path
from test
start with stage_from = 1
connect by stage_from = prior stage_to)
where end = 9;输出：
PATH
---------------------------------------
=> 1 => 2 => 4 => 5 => 6 => 7 => 9
=> 1 => 2 => 4 => 5 => 6 => 8 => 9
=> 1 => 3 => 5 => 6 => 7 => 9
=> 1 => 3 => 5 => 6 => 8 => 9Stack Overflow用户
发布于 2017-10-31 11:09:13
好的，我的主要问题是'1‘只出现在'From’列中，'9‘只出现在'To’列中。为了解决这个问题，我在as的数据中添加了一个额外的行。显然，这在现实世界中可能行不通。
create table temp1 as
select 1 as frm, 2 as too from dual
union all
select 1, 3 from dual
union all
select 2,4 from dual
union all
select 3,5 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
union all
select 6,7 from dual
union all
select 6,8 from dual
union all
select 7,9 from dual
union all
select 8,9 from dual
union all
select 9, 0 from dual然后是这个(相当混乱的)代码。
select
t1a.frm as s1,
t1b.frm as s2,
t1c.frm as s3,
t1d.frm as s4,
t1e.frm as s5,
t1f.frm as s6,
t1g.frm as s7,
t1h.frm as s8
from
temp1 t1a
left join
temp1 t1b
on T1A.too = t1b.frm
left join
temp1 t1c
on t1b.too = t1c.frm
left join
temp1 t1d
on t1c.too = t1d.frm
left join
temp1 t1e
on t1d.too = t1e.frm
left join
temp1 t1f
on t1e.too = t1f.frm
left join
temp1 t1g
on t1f.too = t1g.frm
left join
temp1 t1h
on t1g.too = t1h.frm
where t1a.frm = 1给出了输出
s1 s2 s3 s4 s5 s6 s7 s8
1 3 5 6 8 9
1 3 5 6 7 9
1 2 4 5 6 7 9
1 2 4 5 6 8 9不确定这是否正是你想要的，但希望它能给你一些想法。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47033430
复制相关文章
相似问题
腾讯云开发者
