2013.01.12

-- @PASS : 根据拓扑表更新TB_DEV_SUBSTATION站电压等级

UPDATE tb_dev_substation a
   SET a.basev =
          (SELECT b.basev
             FROM (SELECT *
                     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)
                   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) b
            WHERE a.name = b.stationname);

SELECT * FROM tb_dev_substation;

-- @PASS : 根据拓扑表更新KEYUSER表电压等级

UPDATE tb_eva_keyuser a
   SET a.basev =
          (SELECT b.basev
             FROM (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) b
            WHERE a.name = b.stationname);
            
SELECT * FROM tb_eva_keyuser;


SELECT * FROM tb_topo_node WHERE stationname = '转发厂'