-- METHOD 1: 基于主变及进行确定最大BASEV
SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname;
-- METHOD 2: 基于母线确定最大BASEV
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname;
SELECT * FROM tb_topo_line;
-- METHOD 3:联合以上2种方法,确定最大BASEV
-- 更新 TB_DEV_SUBSTATION TABLE
UPDATE tb_dev_substation a
SET a.basev =
(SELECT b.basev
FROM ( SELECT stationname,
DECODE (GREATEST (MAX (tran_ln), MAX (bus)),
500, 0,
220, 1,
110, 2,
35, 3,
10, 4,
1, 5,
6, 6,
7, 0.4,
-1)
BASEV,
MAX (tran_ln) MAX_TRAN_LN,
MAX (bus) MAX_BUS
FROM ( SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname
UNION
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname)
GROUP BY stationname
ORDER BY stationname) b
WHERE a.name = b.stationname);
-- 更新 TB_EVA_KEYUSER TABLE
SELECT * FROM tb_eva_keyuser;
UPDATE tb_eva_keyuser a
SET a.basev =
(SELECT b.basev
FROM ( SELECT stationname,
GREATEST (MAX (tran_ln), MAX (bus)) BASEV,
MAX (tran_ln) MAX_TRAN_LN,
MAX (bus) MAX_BUS
FROM ( SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname
UNION
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname)
GROUP BY stationname
ORDER BY stationname) b
WHERE a.name = b.stationname);
-- METHOD 4: 基于TB_TOPO_LINE / TB_TOPO_NODE 联合得到站的基本电压等级
SELECT stationname, MAX (basev)
FROM ( SELECT stationname, MAX (basev) basev
FROM tb_topo_line
GROUP BY stationname
UNION ALL
SELECT stationname, MAX (basev) basev
FROM tb_topo_node
GROUP BY stationname)
GROUP BY stationname
ORDER BY MAX (basev) DESC;
-- METHOD 5 : 不能通过 METHOD 1~3 判断的站,通过METHOD 4 可以得到
SELECT stationname, MAX (basev)
FROM ( SELECT stationname, MAX (basev) basev
FROM tb_topo_line
GROUP BY stationname
UNION ALL
SELECT stationname, MAX (basev) basev
FROM tb_topo_node
GROUP BY stationname)
WHERE stationname NOT IN
(SELECT a.stationname
FROM ( SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname
UNION
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname) a
GROUP BY a.stationname)
GROUP BY stationname
ORDER BY MAX (basev) DESC;
-- MEHTODE 1+2+5
SELECT *
FROM ( SELECT stationname,
GREATEST (MAX (tran_ln), MAX (bus)) BASEV,
MAX (tran_ln) MAX_TRAN_LN,
MAX (bus) MAX_BUS
FROM ( SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname
UNION
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname)
GROUP BY stationname
ORDER BY stationname)
UNION
SELECT stationname,
MAX (basev) BASEV,
0 MAX_TRAN_LN,
0 MAX_BUS
FROM ( SELECT stationname, MAX (basev) basev
FROM tb_topo_line
GROUP BY stationname
UNION ALL
SELECT stationname, MAX (basev) basev
FROM tb_topo_node
GROUP BY stationname)
WHERE stationname NOT IN
( SELECT a.stationname
FROM ( SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
FROM tb_topo_line
WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname
UNION
SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
FROM tb_topo_node
WHERE nodetype2 = 11
GROUP BY stationname) a
GROUP BY a.stationname)
GROUP BY stationname;
SELECT *
FROM (SELECT DISTINCT name FROM tb_dev_substation) a
WHERE a.name NOT IN (SELECT DISTINCT stationname FROM tb_topo_line);