嘿,伙计们,我对 SQLplus 相当陌生,目前正试图通过各自的标签名称将值分隔到自己的列中。我已经能够提取过滤掉每个标签名称的列,除了要求的,但我需要每个列只包含相应的标签,而不是 where 子句下的每个标签。
Current Iteration期望输出:
ts 40FQ8.MV2 40FQ431.MV2
-------------------- -------------- --------------
13-JUN-21 13:49:22.5 4275 4370
13-JUN-21 13:50:22.5 4275 4370
13-JUN-21 13:51:22.5 4275 4370
13-JUN-21 13:52:22.5 4275 4370
13-JUN-21 13:53:22.5 4275 4370
13-JUN-21 13:54:22.5 4275 4370
13-JUN-21 13:55:22.5 4275 4370
13-JUN-21 13:56:22.5 4275 4370
13-JUN-21 13:57:22.5 4275 4370
13-JUN-21 13:58:22.5 4275 4370
13-JUN-21 13:59:22.5 4275 4370
13-JUN-21 14:00:22.5 4275 4370
13-JUN-21 14:01:22.5 4275 4370
13-JUN-21 14:02:22.5 4275 4370
13-JUN-21 14:03:22.5 4275 4370
13-JUN-21 14:04:22.5 4275 4370
13-JUN-21 14:05:22.5 4275 4370
13-JUN-21 14:06:22.5 4275 4370
13-JUN-21 14:07:22.5 4275 4370
13-JUN-21 14:08:22.5 4275 4370
13-JUN-21 14:09:22.5 4275 4370
13-JUN-21 14:10:22.5 4275 4370
13-JUN-21 14:11:22.5 4275 4370
任何帮助都将不胜感激!
如果我正确理解这个问题,那将是这样的:
select
ts,
case when name = '40FQ8.MV2' then value else null end "40FQ8.MV2",
case when name = '40FQ431.MV2' then value else null end "40FQ431.MV2"
from history
where name in ('40FQ8.MV2', '40FQ431.MV2')
and ts between (getdbtime - 14000) and (getdbtime)
因此,经过一些故障排除后,我能够通过使用 group by 子句和 aggregates table 甚至添加了额外的标签来获得我正在寻找的结果。我不确定它是最优雅的解决方案,但它对我来说是诀窍:)
SELECT
ts "Timestamp",
sum(case when name = '40FQ8.MV2' then max else null end) "40FQ8.MV2",
sum(case when name = '40FQ431.MV2' then max else null end) "40FQ431.MV2",
sum(case when name = '40FQ1103.MV2' then max else null end) "40FQ1103.MV2",
sum(case when name = '40R1-1_MS.PV' then max else null end) "40R1-1_MS.PV",
sum(case when name = '40R1-2_MS.PV' then max else null end) "40R1-2_MS.PV",
sum(case when name = '40R1-3MS.PV' then max else null end) "40R1-3MS.PV",
sum(case when name = '40R1-1PROG.MV2' then max else null end) "40R1-1PROG.MV2",
sum(case when name = '40R1-2PROG.MV2' then max else null end) "40R1-2PROG.MV2",
sum(case when name = '40R1-ROG.MV2' then max else null end) "40R1-ROG.MV2"
FROM aggregates
WHERE
( name = '40FQ8.MV2' OR
name = '40FQ431.MV2' OR
name = '40FQ1103.MV2' OR
name = '40R1-1_MS.PV' OR
name = '40R1-2_MS.PV' OR
name = '40R1-3MS.PV' OR
name = '40R1-1PROG.MV2' OR
name = '40R1-2PROG.MV2' OR
name = '40R1-ROG.MV2' )
and ts between (getdbtime - 14000) and (getdbtime)
GROUP BY ts
ORDER BY ts
输出:
Timestamp 40FQ8.MV2 40FQ431.MV2 40FQ1103.MV2 40R1-1_MS.PV 40R1-2_MS.PV 40R1-3MS.PV 40R1-1PROG.MV2 40R1-2PROG.MV2 40R1-ROG.MV2
-------------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
14-JUN-21 17:04:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:05:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:06:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:07:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:08:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:09:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:10:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:11:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:12:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:13:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:14:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:15:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:16:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:17:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:18:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:19:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:20:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:21:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:22:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:23:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:24:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:25:34.8 4275 4370 4540 6 3 3 30 0 0
14-JUN-21 17:26:34.8 4275 4370 4540 6 3 3 30 0 0
本站系公益性非盈利分享网址,本文来自用户投稿,不代表边看边学立场,如若转载,请注明出处
评论列表(75条)