动态SQL现实一个表中求多列的和

动态SQL现实一个表中求多列的和

 

1、建表（注：96DATA_VALUE字段分别为一天每15分钟的监测数据，避免一天一个用户产生96条数据，可以减少表的数据量，因为全国用电客户巨大）

 

-- Create table
create table EESMP.R_H_CURVE_E
(
  MS_ID          NUMBER(9) not null,
  DATA_ITEM_CODE VARCHAR2(16) not null,
  DATA_DATE      VARCHAR2(8) not null,
  RECORD_NO      NUMBER(5) not null,
  CURVE_DENSITY  VARCHAR2(8),
  DATA_VALUE1    NUMBER(12,4),
  DATA_VALUE2    NUMBER(12,4),
  DATA_VALUE3    NUMBER(12,4),
  DATA_VALUE4    NUMBER(12,4),
  DATA_VALUE5    NUMBER(12,4),
  DATA_VALUE6    NUMBER(12,4),
  DATA_VALUE7    NUMBER(12,4),
  DATA_VALUE8    NUMBER(12,4),
  DATA_VALUE9    NUMBER(12,4),
  DATA_VALUE10   NUMBER(12,4),
  DATA_VALUE11   NUMBER(12,4),
  DATA_VALUE12   NUMBER(12,4),
  DATA_VALUE13   NUMBER(12,4),
  DATA_VALUE14   NUMBER(12,4),
  DATA_VALUE15   NUMBER(12,4),
  DATA_VALUE16   NUMBER(12,4),
  DATA_VALUE17   NUMBER(12,4),
  DATA_VALUE18   NUMBER(12,4),
  DATA_VALUE19   NUMBER(12,4),
  DATA_VALUE20   NUMBER(12,4),
  DATA_VALUE21   NUMBER(12,4),
  DATA_VALUE22   NUMBER(12,4),
  DATA_VALUE23   NUMBER(12,4),
  DATA_VALUE24   NUMBER(12,4),
  DATA_VALUE25   NUMBER(12,4),
  DATA_VALUE26   NUMBER(12,4),
  DATA_VALUE27   NUMBER(12,4),
  DATA_VALUE28   NUMBER(12,4),
  DATA_VALUE29   NUMBER(12,4),
  DATA_VALUE30   NUMBER(12,4),
  DATA_VALUE31   NUMBER(12,4),
  DATA_VALUE32   NUMBER(12,4),
  DATA_VALUE33   NUMBER(12,4),
  DATA_VALUE34   NUMBER(12,4),
  DATA_VALUE35   NUMBER(12,4),
  DATA_VALUE36   NUMBER(12,4),
  DATA_VALUE37   NUMBER(12,4),
  DATA_VALUE38   NUMBER(12,4),
  DATA_VALUE39   NUMBER(12,4),
  DATA_VALUE40   NUMBER(12,4),
  DATA_VALUE41   NUMBER(12,4),
  DATA_VALUE42   NUMBER(12,4),
  DATA_VALUE43   NUMBER(12,4),
  DATA_VALUE44   NUMBER(12,4),
  DATA_VALUE45   NUMBER(12,4),
  DATA_VALUE46   NUMBER(12,4),
  DATA_VALUE47   NUMBER(12,4),
  DATA_VALUE48   NUMBER(12,4),
  DATA_VALUE49   NUMBER(12,4),
  DATA_VALUE50   NUMBER(12,4),
  DATA_VALUE51   NUMBER(12,4),
  DATA_VALUE52   NUMBER(12,4),
  DATA_VALUE53   NUMBER(12,4),
  DATA_VALUE54   NUMBER(12,4),
  DATA_VALUE55   NUMBER(12,4),
  DATA_VALUE56   NUMBER(12,4),
  DATA_VALUE57   NUMBER(12,4),
  DATA_VALUE58   NUMBER(12,4),
  DATA_VALUE59   NUMBER(12,4),
  DATA_VALUE60   NUMBER(12,4),
  DATA_VALUE61   NUMBER(12,4),
  DATA_VALUE62   NUMBER(12,4),
  DATA_VALUE63   NUMBER(12,4),
  DATA_VALUE64   NUMBER(12,4),
  DATA_VALUE65   NUMBER(12,4),
  DATA_VALUE66   NUMBER(12,4),
  DATA_VALUE67   NUMBER(12,4),
  DATA_VALUE68   NUMBER(12,4),
  DATA_VALUE69   NUMBER(12,4),
  DATA_VALUE70   NUMBER(12,4),
  DATA_VALUE71   NUMBER(12,4),
  DATA_VALUE72   NUMBER(12,4),
  DATA_VALUE73   NUMBER(12,4),
  DATA_VALUE74   NUMBER(12,4),
  DATA_VALUE75   NUMBER(12,4),
  DATA_VALUE76   NUMBER(12,4),
  DATA_VALUE77   NUMBER(12,4),
  DATA_VALUE78   NUMBER(12,4),
  DATA_VALUE79   NUMBER(12,4),
  DATA_VALUE80   NUMBER(12,4),
  DATA_VALUE81   NUMBER(12,4),
  DATA_VALUE82   NUMBER(12,4),
  DATA_VALUE83   NUMBER(12,4),
  DATA_VALUE84   NUMBER(12,4),
  DATA_VALUE85   NUMBER(12,4),
  DATA_VALUE86   NUMBER(12,4),
  DATA_VALUE87   NUMBER(12,4),
  DATA_VALUE88   NUMBER(12,4),
  DATA_VALUE89   NUMBER(12,4),
  DATA_VALUE90   NUMBER(12,4),
  DATA_VALUE91   NUMBER(12,4),
  DATA_VALUE92   NUMBER(12,4),
  DATA_VALUE93   NUMBER(12,4),
  DATA_VALUE94   NUMBER(12,4),
  DATA_VALUE95   NUMBER(12,4),
  DATA_VALUE96   NUMBER(12,4),
  DATA_TYPE      VARCHAR2(8) not null
)
tablespace DATA_TEST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table EESMP.R_H_CURVE_E
  is '1) 记录历史曲线数据，包括I类数据和II类数据，各数据类型通过数据项代码区分，每天更新，最新数据为昨天数据。
2) 数据来源于前置机上传，实时数据域转换，以及其它系统接口数据导入。
3) 该实体用于企业用能监测，企业用能分析等。';
-- Add comments to the columns 
comment on column EESMP.R_H_CURVE_E.MS_ID
  is '监测点标识';
comment on column EESMP.R_H_CURVE_E.DATA_ITEM_CODE
  is '数据项代码';
comment on column EESMP.R_H_CURVE_E.DATA_DATE
  is '数据日期';
comment on column EESMP.R_H_CURVE_E.RECORD_NO
  is '记录序号,默认为0';
comment on column EESMP.R_H_CURVE_E.CURVE_DENSITY
  is '曲线采样密度,单位分钟 1分钟,5分钟,10分钟,15分钟,30分钟,60分钟';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE1
  is '数据值1 异常数据用空值表示';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE2
  is '数据值2';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE3
  is '数据值3';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE4
  is '数据值4';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE5
  is '数据值5';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE6
  is '数据值6';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE7
  is '数据值7';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE8
  is '数据值8';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE9
  is '数据值9';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE10
  is '数据值10';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE11
  is '数据值11';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE12
  is '数据值12';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE13
  is '数据值13';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE14
  is '数据值14';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE15
  is '数据值15';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE16
  is '数据值16';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE17
  is '数据值17';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE18
  is '数据值18';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE19
  is '数据值19';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE20
  is '数据值20';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE21
  is '数据值21';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE22
  is '数据值22';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE23
  is '数据值23';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE24
  is '数据值24';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE25
  is '数据值25';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE26
  is '数据值26';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE27
  is '数据值27';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE28
  is '数据值28';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE29
  is '数据值29';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE30
  is '数据值30';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE31
  is '数据值31';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE32
  is '数据值32';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE33
  is '数据值33';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE34
  is '数据值34';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE35
  is '数据值35';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE36
  is '数据值36';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE37
  is '数据值37';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE38
  is '数据值38';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE39
  is '数据值39';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE40
  is '数据值40';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE41
  is '数据值41';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE42
  is '数据值42';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE43
  is '数据值43';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE44
  is '数据值44';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE45
  is '数据值45';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE46
  is '数据值46';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE47
  is '数据值47';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE48
  is '数据值48';
comment on column EESMP.R_H_CURVE_E.DAT