如何在带有多个表的sqlplus中编写涉及的递归子查询以跟踪节点？ [英] How to write involved recursive subquery in sqlplus w/ multiple tables to trace nodes?
问题描述
我正在尝试创建一个报告，以将电路上的负载映射到其各自的电源（变压器）。
I am trying to create a report that maps loads on a circuit to their respective power source (Transformer).
所有设备都在单独的表中并且相互连接在数据库中：负载，开关，线，电线，变压器。
All of the devices are in separate tables and interconnected in the database: Loads, Switches, Lines, Wires, Transformers.
为简单起见，我将展示一个示例，其中负载仅通过开关直接连接到变压器：
For simplicity's sake, I will show an example where a load is connected to a transformer directly through switches only:
加载表：
LoadNumber, SectionNumber, BusNumber
100 54 3000
切换表：
SwitchNumber, FromSectionNumber, ToSectionNumber, State, BusNumber
1 54 105 Closed 3000
2 105 106 Closed 3000
3 106 500 Open 3000
4 105 999 Closed 3000
5 999 700 Closed 3000
变压器表：
TransformerNumber, FromSectionNumber, ToSectionNumber, FromBus, ToBus
5000 800 700 2000 3000
5001 801 701 2000 3000
在上面的示例中，变压器＃5000通过表格中的开关通过连通性连接到负载＃100，并且由于存在闭合电路而通电（闭合开关将变压器和负载连接在一起）
In the above example, Transformer #5000 is connected to Load #100 via connectivity through the switches in the table, and energized because there is a closed circuit (Closed switches linking the transformer and load together).
我正在尝试编写一个查询，该查询可以生成类似于以下内容的输出：
I am trying to write a query that can generate an output similar to something like this:
LoadNumber, TransformerNumber
100 5000
哪个代表负载＃100由＃5000变压器供电
Which represents that Load #100 is being powered by Transformer #5000
我能够使用1个表（开关）成功找到一个查询，找到与它们链接的变压器，但是我有点迷糊了w将所有表都包含进去以获得网络的完整视图。
I was able to successfully get a query using 1 table (Switches) to find the Transformer they're linked to, but I'm a little lost on how to involve all of the tables to get a complete view of the network.
到目前为止，我得到的是：
What I got so far was:
WITH T3 AS
(SELECT SWITCHNAME, SWITCHNUMBER, PSEUDOSWITCH, FROMSECTIONNUMBER, TOSECTIONNUMBER, NORMALSWITCHINGDEVICESTATE, BUSNUMBER
FROM SWITCHINGDEVICE
WHERE UPPER(NORMALSWITCHINGDEVICESTATE) = 'CLOSED'
AND BUSNUMBER = 3484),
T2 AS
(SELECT TRANSFORMERNUMBER, TRANSFORMERNAME, FROMSECTIONNUMBER, TOSECTIONNUMBER, FROMBUSNUMBER, TOBUSNUMBER
FROM TRANSFORMER
WHERE TOBUSNUMBER = 3484),
T4 AS
(SELECT DEVICENUMBER, DEVICENAME, FROMSECTIONNUMBER, TOSECTIONNUMBER, FROMBUSREF, TOBUSREF
FROM PSA_SERIESREACTIVEDEVICE
WHERE TOBUSREF = 3484),
T5 AS
(SELECT LINENUMBER, LINENAME, FROMSECTIONNUMBER, TOSECTIONNUMBER, FROMBUSNUMBER, TOBUSNUMBER
FROM LINE
WHERE TOBUSNUMBER = 3484 AND FROMBUSNUMBER = 3484),
T1 AS
(SELECT A.BUSNUMBER, A.LOADNUMBER, A.LOADNAME, B.TRANSFORMERNUMBER, B.TRANSFORMERNAME
FROM LOADDEFINITION A, TRANSFORMER B
WHERE A.LOADNUMBER = 5572
AND A.BUSNUMBER = B.TOBUSNUMBER
---TEST LOAD
UNION ALL
SELECT T1.BUSNUMBER, T1.LOADNUMBER, T1.LOADNAME, T2.TRANSFORMERNUMBER, T2.TRANSFORMERNAME
FROM LOADDEFINITION T1, TRANSFORMER T2, SWITCHINGDEVICE T3, PSA_SERIESREACTIVEDEVICE T4, LINE T5, SWITCHINGDEVICE T6
WHERE
T1.LOADNUMBER = 5572 AND T1.BUSNUMBER = 3484
AND T2.TOBUSNUMBER = T1.BUSNUMBER
AND T3.BUSNUMBER = T1.BUSNUMBER
AND T4.TOBUSREF = T1.BUSNUMBER
AND T5.TOBUSNUMBER = T1.BUSNUMBER
AND T5.FROMBUSNUMBER = T5.TOBUSNUMBER
AND T3.BUSNUMBER = T2.TOBUSNUMBER
AND T4.TOBUSREF = T2.TOBUSNUMBER
AND T5.TOBUSNUMBER = T2.TOBUSNUMBER
AND T3.BUSNUMBER = T4.TOBUSREF
AND T5.TOBUSNUMBER = T3.BUSNUMBER
AND T5.TOBUSNUMBER = T4.TOBUSREF
AND T6.BUSNUMBER = T1.BUSNUMBER
---take into consideration downstream loops
AND ((T1.SECTIONNUMBER = T3.TOSECTIONNUMBER AND T1.SECTIONNUMBER = T6.TOSECTIONNUMBER)
OR
(T1.SECTIONNUMBER = T3.FROMSECTIONNUMBER AND T1.SECTIONNUMBER = T6.FROMSECTIONNUMBER))
AND (T3.FROMSECTIONNUMBER = T2.TOSECTIONNUMBER)
AND (T3.FROMSECTIONNUMBER = T6.TOSECTIONNUMBER)
AND T6.NORMALSWITCHINGDEVICESTATE = T3.NORMALSWITCHINGDEVICESTATE
)
SELECT T1.BUSNUMBER, T1.LOADNUMBER, T1.LOADNAME, T2.TRANSFORMERNUMBER, T2.TRANSFORMERNAME FROM T1, T2
但不要像我期望的那样返回1个结果（由于我对单个负载进行了硬编码），所以我得到了一个显示所有变压器的列表。
But rather than returning back 1 result as I would expect (Since I hardcoded a single load), I'm getting back a list showing all of the transformers.
用示例数据表示，结果看起来像： / p>
Representing that with the example data, the result looks something like:
LoadNumber, TransformerNumber
100 5000
100 5001
我已经玩了一段时间了，不确定这个查询的哪一部分搞砸了。
I've been playing around with this for some time and not sure which part of this query is messed up.
编辑：
如果我想使用示例数据集编写查询以获取所需结果，如何会实现吗？
If I wanted to write a query to obtain the desired result using the example dataset, how would that be accomplished?
具有以下逻辑：选择loadnumber，transformernumber，其中负载通过闭合的开关设备与变压器进行实时连接？
Something with the logic of: select loadnumber, transformernumber where load has a live connection to the transformer through closed switch devices?
推荐答案
在您的简化示例（某种程度上，我开始使用您的真实表名，但这是原始数据）中，您可以通过以下方式通过多个交换机获得所有路由： / p>
In your simplified example (sort of, I started to use your real table names, but it's the original data) you can get all of the routes through multiple switches with:
with rcte (rootsectionnumber, fromsectionnumber, tosectionnumber) as (
select fromsectionnumber, fromsectionnumber, tosectionnumber
from switchingdevice
where normalswitchingdevicestate = 'CLOSED'
union all
select r.rootsectionnumber, sd.fromsectionnumber, sd.tosectionnumber
from rcte r
join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
)
select rootsectionnumber, tosectionnumber from rcte;
ROOTSECTIONNUMBER TOSECTIONNUMBER
----------------- ---------------
54 105
105 106
105 999
999 700
54 106
105 500
54 999
105 700
54 500
54 700
其中一个具有从第54条到700;然后可以将其加入到负载定义中：
one of which has an end-to-end route from section 54 to 700; and you can then join that to the load definition:
with rcte (rootsectionnumber, fromsectionnumber, tosectionnumber) as (
select fromsectionnumber, fromsectionnumber, tosectionnumber
from switchingdevice
where normalswitchingdevicestate = 'CLOSED'
union all
select r.rootsectionnumber, sd.fromsectionnumber, sd.tosectionnumber
from rcte r
join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
)
select ld.loadnumber, t.transformernumber
from loaddefinition ld
join rcte r on r.rootsectionnumber = ld.sectionnumber
join transformer t on t.tosectionnumber = r.tosectionnumber
where ld.loadnumber = 100;
LOADNUMBER TRANSFORMERNUMBER
---------- -----------------
100 5000
或者如果您需要在逻辑中包含 busnumber （基于对较大查询的简要介绍...）：
Or if you need to include the busnumber in the logic (based on a brief look at your larger query...):
with rcte (rootsectionnumber, busnumber, fromsectionnumber, tosectionnumber) as (
select fromsectionnumber, busnumber, fromsectionnumber, tosectionnumber
from switchingdevice
where normalswitchingdevicestate = 'CLOSED'
union all
select r.rootsectionnumber, r.busnumber, sd.fromsectionnumber, sd.tosectionnumber
from rcte r
join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
and sd.busnumber = r.busnumber
)
select ld.loadnumber, t.transformernumber
from loaddefinition ld
join rcte r on r.rootsectionnumber = ld.sectionnumber
and r.busnumber = ld.busnumber
join transformer t on t.tosectionnumber = r.tosectionnumber
and t.tobusnumber = r.busnumber
where ld.loadnumber = 100;
LOADNUMBER TRANSFORMERNUMBER
---------- -----------------
100 5000
添加到其他表中显然会使事情复杂一些，但是还不清楚如何它们都是相关的。
Adding in your other tables will obviously complicate things a bit, but it isn't clear quite how they're all related.
这篇关于如何在带有多个表的sqlplus中编写涉及的递归子查询以跟踪节点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
