有如下ST_WLAN_CPUSR_CNT_D表和ST_WLAN_RDS_CPUSR_INFO_D表:
create table ST_WLAN_CPUSR_CNT_D (
DATE8 VARCHAR2(8),
HOSTNAME VARCHAR2(20),
USERCITY VARCHAR2(20),
SUM_REGUSRCNT NUMBER,
SUM_ACTUSRCNT NUMBER
);
create table ST_WLAN_RDS_CPUSR_INFO_D(
DATE8 VARCHAR2(8),
HOSTNAME VARCHAR2(20),
ACIP VARCHAR2(20),
SUM_USERDT NUMBER,
SUM_USERTHP NUMBER
);
create table ST_WLAN_CPUSR_CNT_D (
DATE8 VARCHAR2(8),
HOSTNAME VARCHAR2(20),
USERCITY VARCHAR2(20),
SUM_REGUSRCNT NUMBER,
SUM_ACTUSRCNT NUMBER
);
create table ST_WLAN_RDS_CPUSR_INFO_D(
DATE8 VARCHAR2(8),
HOSTNAME VARCHAR2(20),
ACIP VARCHAR2(20),
SUM_USERDT NUMBER,
SUM_USERTHP NUMBER
);
values ('20110830', 'itellin120', '0', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110830', 'itellin120', 'ALL', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110905', 'Portal_Host', '10.137.7.118', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110905', 'Portal_Host', '10.137.7.130', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110830', 'itellin120', 'nanjing', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110830', 'itellin120', 'shanghai', 36, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110831', 'itellin120', 'nanjing', 244, 72);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110831', 'itellin120', 'shanghai', 242, 119);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110905', 'Portal_Host', '0', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110906', 'Portal_Host', '10.137.7.118', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110831', 'itellin120', '0', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110901', 'itellin120', 'nanjing', 780, 330);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110901', 'itellin120', 'shanghai', 260, 149);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110906', 'Portal_Host', '10.137.7.130', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110901', 'itellin120', '0', 0, 0);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110902', 'itellin120', 'nanjing', 513, 342);
insert into ST_WLAN_CPUSR_CNT_D (DATE8, HOSTNAME, USERCITY, SUM_REGUSRCNT, SUM_ACTUSRCNT)
values ('20110902', 'itellin120', 'shanghai', 171, 171);
values ('20110830', 'itellin120', '0', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110830', 'itellin120', '10.137.7.67', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110830', 'itellin120', '10.137.7.66', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110905', 'Portal_Host', '10.137.7.130', 0, 24);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110905', 'Portal_Host', '0', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110831', 'itellin120', '10.137.7.67', 153, 9819);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110831', 'itellin120', '10.137.7.66', 102, 9817);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110905', 'Portal_Host', '10.137.7.118', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110905', 'Portal_Host', '10.138.6.29', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110906', 'Portal_Host', '10.137.7.118', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110831', 'itellin120', '0', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110901', 'itellin120', '10.137.7.67', 0, 4905);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110901', 'itellin120', '10.137.7.66', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110906', 'Portal_Host', '10.137.7.130', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110901', 'itellin120', '0', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110902', 'itellin120', '10.137.7.67', 0, 0);
insert into ST_WLAN_RDS_CPUSR_INFO_D (DATE8, HOSTNAME, ACIP, SUM_USERDT, SUM_USERTHP)
values ('20110902', 'itellin120', '10.137.7.66', 0, 0);
commit;
Select
a.DATE8 as T0,
sum(a.SUM_REGUSRCNT) as T1,
sum(a.SUM_ACTUSRCNT) as T2,
sum(b.SUM_USERDT)/60 as T3,
sum(b.SUM_USERTHP)/1024 as T4
from ST_WLAN_CPUSR_CNT_D a full join ST_WLAN_RDS_CPUSR_INFO_D b on a.date8 = b.date8
group by a.date8;
但这样写数据就错了。sum(a.SUM_REGUSRCNT)的值多了3倍,关联有很大的问题!
请教各位大侠该怎么写?
换一种连接吧