Aspen案例:AspenSQLPlus:按名称分隔列

嘿,伙计们,我对 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

任何帮助都将不胜感激!

0

如果我正确理解这个问题,那将是这样的:

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)
0

因此,经过一些故障排除后,我能够通过使用 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

本站系公益性非盈利分享网址,本文来自用户投稿,不代表边看边学立场,如若转载,请注明出处

(581)
Oracle数据库接口:使用DataSource接口连接Oracle数据库的程序
上一篇
动态sql:什么是动态 SQL(what is dynamic sql)
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(75条)