建临时表查数据

/*
TRUNCATE TABLE TMP_ABC_FGSAna;
DROP TABLE TMP_ABC_FGSAna;
/
CREATE GLOBAL TEMPORARY TABLE TMP_ABC_FGSAna
(
  fgsID varchar2(64),
  FgsName varchar2(64),
  fldorder number,
  bCel number,
  cCel number,
  dCel number,
  eCel number,
  fCel number,
  gCel number,
  hCel number,
  iCel number,
  jCel number,
  kCel number,
  lCel number,
  mCel number,
  nCel number,
  oCel number,
  pCel number,
  qCel number,
  rCel number,
  sCel number,
  tCel number,
  uCel number,
  vCel number,
  wCel number,
  xCel number,
  yCel number,
  zCel number,
  aaCel number,
  abCel number,
  acCel number
) ON COMMIT preserve ROWS;
*/

TRUNCATE TABLE TMP_ABC_FGSAna;
--分公司名称
insert into TMP_ABC_FGSAna(fgsid,fgsname,fldorder) select fldid,fldcareaname,fldorder from tar_area where fldparentid is null;
---
--1楼宇总数
update TMP_ABC_FGSAna t set bCel=(select count(*) from tar_station s where s.fldex9=t.fgsid and flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3'));
--租户数总数
update TMP_ABC_FGSAna t set cCel=(select SUM(fldex38)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and fldex38 !='不详');
--2G室分总完成数
update TMP_ABC_FGSAna t set dCel=(select count(fldex17)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and fldex17='1');
--3G室分总完成数
update TMP_ABC_FGSAna t set eCel=(select count(fldex19)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and fldex19='1');
--WLAN总完成数
update TMP_ABC_FGSAna t set fCel=(select count(fldex21)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and fldex21='1');
--楼宇专线总完成数
update TMP_ABC_FGSAna t set gCel=(select count(FLDEX29)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDEX29='6');
--IPTV总完成数
update TMP_ABC_FGSAna t set hCel=(select count(FLDEX67)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDEX67='1');
--A类楼宇总数
update TMP_ABC_FGSAna t set iCel=(select count(*)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20));
--A类租户数总数
update TMP_ABC_FGSAna t set jCel=(select SUM(fldex38)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex38 !='不详');
--A类二G室分数量
update TMP_ABC_FGSAna t set kCel=(select count(fldex17)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex17='1');
--A类三G室分数量
update TMP_ABC_FGSAna t set lCel=(select count(fldex19)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex19='1');
--A类WLAN
update TMP_ABC_FGSAna t set mCel=(select count(fldex21)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex21='1');
--A类楼宇专线总数
update TMP_ABC_FGSAna t set nCel=(select count(FLDEX29)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and FLDEX29='6');
--A类IPTV总数
update TMP_ABC_FGSAna t set oCel=(select count(FLDEX67)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (7,12,8,9,3,11,5,2,13,1,10,20) and FLDEX67='1');
---==B
--B类楼宇总数
update TMP_ABC_FGSAna t set pCel=(select count(*)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4));
--B类租户数总数
update TMP_ABC_FGSAna t set qCel=(select SUM(fldex38)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and fldex38 !='不详');
--B类二G室分数量
update TMP_ABC_FGSAna t set rCel=(select count(fldex17)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and fldex17='1');
--B类三G室分数量
update TMP_ABC_FGSAna t set sCel=(select count(fldex19)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and fldex19='1');
--B类WLAN
update TMP_ABC_FGSAna t set tCel=(select count(fldex21)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and fldex21='1');
--B类楼宇专线总数
update TMP_ABC_FGSAna t set uCel=(select count(FLDEX29)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and FLDEX29='6');
--B类IPTV总数
update TMP_ABC_FGSAna t set vCel=(select count(FLDEX67)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (6,14,15,33,19,21,22,4) and FLDEX67='1');
------------C
--C类租户数总数
update TMP_ABC_FGSAna t set wCel=(select SUM(fldex38)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and fldex38 !='不详');
--C类二G室分数量
update TMP_ABC_FGSAna t set xCel=(select count(fldex17)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and fldex17='1');
--C类三G室分数量
update TMP_ABC_FGSAna t set yCel=(select count(fldex19)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and fldex19='1');
--C类WLAN
update TMP_ABC_FGSAna t set zCel=(select count(fldex21)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and fldex21='1');
--C类楼宇总数
update TMP_ABC_FGSAna t set aaCel=(select count(*)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31));
--C类楼宇专线总数
update TMP_ABC_FGSAna t set abCel=(select count(FLDEX29)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and FLDEX29='6');
--C类IPTV总数
update TMP_ABC_FGSAna t set acCel=(select count(FLDEX67)from tar_station s where s.fldex9=t.fgsid and  flditype is not null and FLDISFILLING='1' AND FLDBUILDSTATUS not in ('5','3')
and FLDITYPE in (18,29,24,26,17,25,27,28,32,30,31) and FLDEX67='1');
--查询
select * from TMP_ABC_FGSAna order by fldorder;