2013.01.11 ORA-SQL 测试脚本

  -- 基于主变及进行确定最大BASEV

  SELECT stationname, MAX (basev) TRAN_LN, 0 BUS
    FROM tb_topo_line
   WHERE linetype IN (12, 271, 527, 783)
GROUP BY stationname;

-- 基于母线确定最大BASEV

  SELECT stationname, 0 TRAN_LN, MAX (basev) BUS
    FROM tb_topo_node
   WHERE nodetype2 = 11
GROUP BY stationname;

-- 联合以上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);

SELECT *
  FROM (SELECT DISTINCT name FROM tb_dev_substation) a
 WHERE a.name NOT IN (SELECT DISTINCT stationname FROM tb_topo_line);