SELECT * FROM tb_scada_data;SELECT * FROM tb_topo_line WHERE linetype = 12;SELECT * FROM tb_topo_node WHERE id IN (8712, 8713);SELECT * FROM tb_topo_line WHERE linetype IN (271, 527, 783);SELECT * FROM tb_topo_node WHERE id IN (90, 91); SELECT p.*, COUNT (*) FROM tb_topo_node p WHERE name = '变压器中性点'GROUP BY stationname; SELECT stationname, MAX (row_index) FROM (SELECT p.*, ROW_NUMBER () OVER (PARTITION BY stationname ORDER BY id) AS row_index FROM tb_topo_node p WHERE name = '变压器中性点')GROUP BY stationname;-- OVER SELECT stationname, SUM (DECODE (linetype, 12, MAX_BY_DEV, 0)) MAX_BY_LINE, SUM (DECODE (linetype, 13, MAX_BY_DEV, 0)) MAX_BY_BRK, SUM (DECODE (linetype, 14, MAX_BY_DEV, 0)) MAX_BY_DISCON, SUM (DECODE (linetype, 16, MAX_BY_DEV, 0)) MAX_BY_CAP, SUM (DECODE (linetype, 273, MAX_BY_DEV, 783, MAX_BY_DEV, 0)) MAX_BY_TH, SUM (DECODE (linetype, 527, MAX_BY_DEV, 0)) MAX_BY_TM FROM (SELECT MAX_BY_DEV, LINETYPE, NAME, STATIONNAME FROM (SELECT ROW_NUMBER () OVER (PARTITION BY stationname, linetype ORDER BY linetype) AS dev_index, MAX ( basev) OVER (PARTITION BY stationname, linetype ORDER BY basev) MAX_BY_DEV, p.* FROM tb_topo_line p) WHERE dev_index = 1)GROUP BY stationname;-- 按站分析各类设备最大电压等级SELECT * FROM ( SELECT stationname, MAX (MAX_BY_TRAN) MAXV_BY_TRAN, SUM (MAX_BY_LINE) MAXV_BY_LINE, SUM (MAX_BY_BRK) MAXV_BY_BRK, SUM (MAX_BY_DISCON) MAXV_BY_DISCON, SUM (MAX_BY_CAP) MAXV_BY_CAP FROM ( SELECT stationname, DECODE (linetype, 12, MAX (basev), 0) MAX_BY_LINE, DECODE (linetype, 13, MAX (basev), 0) MAX_BY_BRK, DECODE (linetype, 14, MAX (basev), 0) MAX_BY_DISCON, DECODE (linetype, 16, MAX (basev), 0) MAX_BY_CAP, DECODE (linetype, 271, MAX (basev), 783, MAX (basev), 0) MAX_BY_TRAN FROM tb_topo_line GROUP BY stationname, linetype ORDER BY stationname) GROUP BY stationname ORDER BY stationname) a NATURAL JOIN ( SELECT stationname, MAX (basev) MAX_BY_NODE FROM tb_topo_node GROUP BY stationname) b; SELECT stationname, MAX (basev) FROM tb_topo_lineGROUP BY stationname; SELECT stationname, CASE WHEN MAX (tran_ln) > MAX (bus) THEN MAX (tran_ln) ELSE MAX (bus) END basev, MAX (tran_ln), 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 (SELECT * FROM (SELECT * FROM tb_topo_node MINUS SELECT a.* FROM tb_topo_node a, tb_topo_line b WHERE a.name = b.name AND a.stationname = b.stationname) WHERE name LIKE '%母%' OR name LIKE '%正%' OR name LIKE '%副%') GROUP BY stationname ORDER BY stationname)GROUP BY stationname;SELECT a. name, a. basev, b.basev FROM tb_eva_keyuser a, ( SELECT stationname, CASE WHEN MAX (tran_ln) > MAX (bus) THEN MAX (tran_ln) ELSE MAX (bus) END basev 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 (SELECT * FROM (SELECT * FROM tb_topo_node MINUS SELECT a.* FROM tb_topo_node a, tb_topo_line b WHERE a.name = b.name AND a.stationname = b.stationname) WHERE name LIKE '%母%' OR name LIKE '%正%' OR name LIKE '%副%') GROUP BY stationname ORDER BY stationname)GROUP BY stationname)bWHERE a.name = b.stationname and a.basev <> b.basev; -- and b.linetype in (12, 271, 527, 783);SELECT * FROM tb_topo_line;
