我正在尝试创建一个报告,将电路上的负载映射到各自的电源(变压器)。
所有设备都在单独的表中,并在数据库中互连:负载、开关、线路、电线、变压器。
为简单起见,我将展示一个仅通过开关将负载直接连接到变压器的示例:
负载表:
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,并且由于存在闭合电路(将变压器和负载连接在一起的闭合开关)而通电。
我正在尝试编写一个可以生成类似于以下内容的输出的查询:
LoadNumber, TransformerNumber
100 5000
这表示负载 #100 由变压器 #5000 供电
我能够使用 1 个表(交换机)成功获取查询以查找它们链接到的转换器,但我对如何涉及所有表以获得网络的完整视图有点迷茫。
到目前为止,我得到的是:
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 个结果(因为我对单个负载进行了硬编码),而是返回了一个显示所有转换器的列表。
用示例数据表示,结果如下所示:
LoadNumber, TransformerNumber
100 5000
100 5001
我已经玩了一段时间了,不确定这个查询的哪个部分搞砸了。
编辑:
如果我想编写查询以使用示例数据集获得所需的结果,如何实现?
逻辑是:选择负载编号,变压器编号,其中负载通过闭合开关设备与变压器有实时连接?
在您的简化示例中(有点,我开始使用您的真实表名,但这是原始数据),您可以使用以下方法通过多个交换机获取所有路由:
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 节的端到端路由;然后您可以将其连接到负载定义:
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
(基于对较大查询的简要了解...
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
数据库<>小提琴
添加其他表显然会使事情变得有点复杂,但尚不清楚它们是如何相关的。