2013.01.11 ORA-SQL 测试脚本2

  -- 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);