存储过程紧急求助啊!

显示全部楼层 · 2012-5-21 10:19:41

紧急求助,
--存储了系统信息
CREATE TABLE CZSYSTEMM1
(
"ID" VARCHAR2(60),

"SHTEL_CZSYSTEM" VARCHAR2(60)
)
--存储了产品信息
CREATE TABLE CZPRODUCTM1
(
"ID" VARCHAR2(60),

"SHTEL_CZPRODUCT" VARCHAR2(60)
)
--存储了问题信息
CREATE TABLE CZPROBLEMTYPEM1
(
"ID" VARCHAR2(60),

"SHTEL_CZPROBLEMTYPE" VARCHAR2(60)
)
-- 存储事件信息
CREATE TABLE PROBSUMMARYM1
(

"CZPROBLEMTYPE" VARCHAR2(60),

"CZPRODUCT" VARCHAR2(60),

"CZSYSTEM" VARCHAR2(60),

"CHANNEL" VARCHAR2(50)
)
其中关联关系如下
PROBSUMMARYM1.CZPRODUCT=CZPRODUCTM1.SHTEL_CZPRODUCT
PROBSUMMARYM1.CZPROBLEMTYPE=CZPROBLEMTYPEM1.SHTEL_CZPROBLEMTYPE
PROBSUMMARYM1.CZSYSTEM=CZSYSTEMM1.SHTEL_CZSYSTEM
-- 聚合的表
CREATE TABLE PROBAGGREGATION (

"ID" VARCHAR2(60),
"CZPRODUCT" VARCHAR2(60),

"CZPROBLEMTYPE" VARCHAR2(60),

"CZSYSTEM" VARCHAR2(60),
"TOTAL" NUMBER
)
问题如下:
我想嵌套遍历前3个表,并查询对应的记录在第四个表中,是否存在,不存在, 在0,存在+1, 所有嵌套查询的结果插入表PROBAGGREGATION
因为确实没怎么用过存储过程,紧急求助各位,帮帮忙,谢谢.

千问 · 2012-5-21 10:19:41

select rownum+(select count(*)from PROBAGGREGATION)ID, SHTEL_CZPRODUCT CZPRODUCT ,SHTEL_CZPROBLEMTYPE CZPROBLEMTYPE
,SHTEL_CZSYSTEM CZSYSTEM,case when cnt=0 then 0 else cnt+1 end TOTAL
from (
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,count
(c.L)CNT from(
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,SHTEL_CZPRODUCT||SHTEL_CZPROBLEMTYPE||SHTEL_CZSYSTEM K
FROM CZPRODUCTM1,CZPROBLEMTYPEM1,CZSYSTEMM1)a,
(select CZPRODUCT||CZPROBLEMTYPE||CZSYSTEM L from PROBSUMMARYM1)c
where
c.L(+)=
a.K
group by SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM
)b

千问 · 2012-5-21 10:19:41

Hi:

我试了你的sql, 是可以将所有组合都列出来,但最后的统计值都是0:
select rownum+(select count(*)from PROBAGGREGATION)ID, SHTEL_CZPRODUCT CZPRODUCT ,SHTEL_CZPROBLEMTYPE CZPROBLEMTYPE
,SHTEL_CZSYSTEM CZSYSTEM,case when cnt=0 then 0 else cnt+1 end TOTAL
from (
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,count
(c.L)CNT from(
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,SHTEL_CZPRODUCT||SHTEL_CZPROBLEMTYPE||SHTEL_CZSYSTEM K
FROM shtelePRODUCTM1,shtelePROBLEMTYPEM1,shtelesystemm1)a,
(select shtel_CZPRODUCT||shtel_CZPROBLEMTYPE||shtel_CZSYSTEM L from PROBSUMMARYM1)c
where
c.L(+)=
a.K
group by SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM
)b
部分数据如下:
1
4
C网产品
原数据问题
BILLING
0
XXXXX
U
2
5
C网产品
功能软件问题
UVC
0
XXXXX
U
3
6
C网产品
功能软件问题
OCS
0
XXXXX
U
4
7
C网产品
接口软件问题
积分平台
0
XXXXX
U
5
8
C网产品
接口软件问题
EDW
0
XXXXX
U

千问 · 2012-5-21 10:19:41

不好意思,刚才我看错了,有不是0的,但感觉不对
select shtel_czproduct, shtel_czproblemtype, shtel_czsystem from probsummarym1 group by shtel_czproduct, shtel_czproblemtype, shtel_czsystem order by shtel_czproduct, shtel_czproblemtype, shtel_czsystem;
1
C网产品
业务问题
HB
2
C网产品
功能软件问题
CQD
3
固网产品
网元问题
非IT
4
融合产品
业务问题
CQD
5
融合产品
其它问题
CRM
6

上面的是源数据

select * from (select rownum+(select count(*)from PROBAGGREGATION)ID, SHTEL_CZPRODUCT CZPRODUCT ,SHTEL_CZPROBLEMTYPE CZPROBLEMTYPE
,SHTEL_CZSYSTEM CZSYSTEM,case when cnt=0 then 0 else cnt+1 end TOTAL
from (
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,count
(c.L)CNT from(
select SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM,SHTEL_CZPRODUCT||SHTEL_CZPROBLEMTYPE||SHTEL_CZSYSTEM K
FROM shtelePRODUCTM1,shtelePROBLEMTYPEM1,shtelesystemm1)a,
(select shtel_CZPRODUCT||shtel_CZPROBLEMTYPE||shtel_CZSYSTEM L from PROBSUMMARYM1)c
where
c.L(+)=
a.K
group by SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM
)b) t where t.total !=0
执行结果:
1
217
融合产品
其它问题
CRM
2
2
251
C网产品
业务问题
HB
2
3
304
C网产品
功能软件问题
CQD
2
4
370
融合产品
业务问题
CQD
2
好像和源数据不匹配,但有不是0的, 奇怪,呵呵
求助哦!谢谢

千问 · 2012-5-21 10:19:41

是否存在,不存在, 在0,存在+1
是什么意思，用数据举例

千问 · 2012-5-21 10:19:41

非常感谢〇〇,我的问题已得到解决.

千问 · 2012-5-21 10:19:41

LZ,你应该先看看“http://www.itpub.net/thread-1063592-1-1.html”！

千问 · 2012-5-21 10:19:41

发现个问题:
如果SHTEL_CZPRODUCT ,SHTEL_CZPROBLEMTYPE ,SHTEL_CZSYSTEM中有空值的是没法统计出来的, 因为组合起来的话再group by 统计是有问题的

存储过程 紧急求助啊!

存储过程紧急求助啊!