2013.01.11.SJ. ORA-SQL SCRIPT

-- M1

  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;


-- M2

  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;

-- M1 + 2

  SELECT *
    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)
ORDER BY basev DESC;


SELECT tl.*,
       ROW_NUMBER ()
          OVER (PARTITION BY stationname, linetype ORDER BY basev DESC, name)
          AS row_index
  FROM tb_topo_line tl
 WHERE stationname = '大学站';

SELECT * FROM tb_topo;

  SELECT *
    FROM tb_topo_node
   WHERE stationname = '黄渡'
ORDER BY nodetype2, name;

SELECT DISTINCT stationname FROM tb_topo_node;


SELECT *
  FROM tb_topo_node
 WHERE stationname = '新增中间节点';