mysql sum 几个列可以为负值吗

转载

mob64ca1404ed65 2024-10-09 10:30:48

文章标签 mysql sum 几个列可以为负值吗数据值数据数据项 文章分类 MySQL 数据库

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.DATA_VALUE49
  is '数据值49';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE50
  is '数据值50';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE51
  is '数据值51';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE52
  is '数据值52';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE53
  is '数据值53';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE54
  is '数据值54';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE55
  is '数据值55';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE56
  is '数据值56';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE57
  is '数据值57';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE58
  is '数据值58';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE59
  is '数据值59';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE60
  is '数据值60';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE61
  is '数据值61';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE62
  is '数据值62';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE63
  is '数据值63';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE64
  is '数据值64';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE65
  is '数据值65';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE66
  is '数据值66';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE67
  is '数据值67';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE68
  is '数据值68';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE69
  is '数据值69';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE70
  is '数据值70';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE71
  is '数据值71';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE72
  is '数据值72';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE73
  is '数据值73';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE74
  is '数据值74';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE75
  is '数据值75';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE76
  is '数据值76';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE77
  is '数据值77';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE78
  is '数据值78';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE79
  is '数据值79';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE80
  is '数据值80';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE81
  is '数据值81';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE82
  is '数据值82';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE83
  is '数据值83';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE84
  is '数据值84';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE85
  is '数据值85';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE86
  is '数据值86';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE87
  is '数据值87';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE88
  is '数据值88';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE89
  is '数据值89';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE90
  is '数据值90';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE91
  is '数据值91';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE92
  is '数据值92';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE93
  is '数据值93';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE94
  is '数据值94';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE95
  is '数据值95';
comment on column EESMP.R_H_CURVE_E.DATA_VALUE96
  is '数据值96';
comment on column EESMP.R_H_CURVE_E.DATA_TYPE
  is '数据类型，区分统一数据项代码的不同曲线数据,引用标准代码曲线数据类型，01-示值，02-能量，99-其他。';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EESMP.R_H_CURVE_E
  add constraint PK_R_H_CURVE_1 primary key (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, DATA_TYPE)
  using index ;

2、插数据（只插入几条测试数据）

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, 
DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, 
DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, 
DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44,
DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56,
DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68,
DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, DATA_VALUE79, DATA_VALUE80,
DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,
DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)
values (1040, '9101', '20130424', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900,
69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500,
54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,
6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,
24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, 
DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, 
DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36,
DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50,
DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64,
DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78,
DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,
DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)
values (1040, '9101', '20130425', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 
69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 
54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,
6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,
24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9,
DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, 
DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, DATA_VALUE37,
DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, DATA_VALUE51,
DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, DATA_VALUE65,
DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, DATA_VALUE79,
DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92, DATA_VALUE93,
DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)
values (1040, '9101', '20130426', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900,
69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500,
54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,
6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,
24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, 
DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22,
DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36,
DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50,
DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64,
DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78,
DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,
DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)
values (1040, '9101', '20130427', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 
69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 
54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500, 
6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400, 
24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, 
DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, 
DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, 
DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, 
DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, 
DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, 
DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92, 
DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)
values (1040, '9101', '20130428', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 
69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 
54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500, 
6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400, 
24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

3、现实需求的PL/SQL块

DECLARE
  I          NUMBER;
  RESULT     NUMBER := 0;
  A          VARCHAR2(20);
  SUNLINE    VARCHAR2(4000);
  DATA_VALUE VARCHAR2(20);
BEGIN
  FOR I IN 1 .. 3 LOOP
    a := 'DATA_VALUE' || I;
    EXECUTE IMMEDIATE 'SELECT sum(' || A ||
                      ') FROM R_H_CURVE_E a WHERE substr(a.data_date,1,6)=201304 AND a.data_item_code=9101 AND ms_id=1040'
      INTO SUNLINE;
    RESULT := RESULT + SUNLINE;
    DBMS_OUTPUT.PUT_LINE(RESULT);
  END LOOP;
END;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。