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_line
GROUP 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
)b
WHERE a.name = b.stationname and a.basev <> b.basev;
-- and b.linetype in (12, 271, 527, 783);
SELECT * FROM tb_topo_line;
2013.01.10 SQL测试脚本
最新推荐文章于 2023-02-28 18:23:24 发布