SELECT aa.fdepartment 部门,
SUM (aa.tot_a) "是否在a刊物发表文章数量",
SUM (aa.a_num) "是否在a刊物发表文章数量",
SUM (aa.tot_b) "是否在b刊物发表文章数量",
SUM (aa.b_num) "是否在b刊物发表文章数量",
nvl(bb.FRELEASECOUNT,0),
cc.tot
FROM (SELECT a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
SUM (a.fhomepagetorf) b_num
FROM oa_inforelease a,
(SELECT a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
last_m
FROM oa_inforelease a
WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
'yyyy-mm-dd')
AND TO_DATE ('2008-7-2',
'yyyy-mm-dd')
GROUP BY a.fdepartment) b
WHERE a.fdepartment = b.fdepartment
AND TRUNC (a.fdate, 'mm') = b.last_m
GROUP BY a.fdepartment
UNION ALL
SELECT a.fdepartment, SUM (a.femittorf) tot_a, 0,
SUM (a.fhomepagetorf) tot_b, 0
FROM oa_inforelease a
WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
AND TO_DATE ('2008-7-2', 'yyyy-mm-dd')
GROUP BY a.fdepartment) aa,
(
select a.FDEPARTMENT,a.FRELEASECOUNT
from OA_DEPTRELEASE a
) bb,
(
select nvl(sum(a.FRELEASECOUNT),0) tot
from OA_DEPTRELEASE a
) cc
where aa.FDEPARTMENT=bb.FDEPARTMENT(+)
GROUP BY aa.fdepartment,nvl(bb.FRELEASECOUNT,0),cc.tot
注:
现在语句:cc.tot字段统计的是OA_DEPTRELEASE.FRELEASECOUNT全部数量。
正确结果应为:cc.tot字段统计 查询出的显示结果部门的FRELEASECOUNT字段的总和。
SUM (aa.tot_a) "是否在a刊物发表文章数量",
SUM (aa.a_num) "是否在a刊物发表文章数量",
SUM (aa.tot_b) "是否在b刊物发表文章数量",
SUM (aa.b_num) "是否在b刊物发表文章数量",
nvl(bb.FRELEASECOUNT,0),
cc.tot
FROM (SELECT a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
SUM (a.fhomepagetorf) b_num
FROM oa_inforelease a,
(SELECT a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
last_m
FROM oa_inforelease a
WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
'yyyy-mm-dd')
AND TO_DATE ('2008-7-2',
'yyyy-mm-dd')
GROUP BY a.fdepartment) b
WHERE a.fdepartment = b.fdepartment
AND TRUNC (a.fdate, 'mm') = b.last_m
GROUP BY a.fdepartment
UNION ALL
SELECT a.fdepartment, SUM (a.femittorf) tot_a, 0,
SUM (a.fhomepagetorf) tot_b, 0
FROM oa_inforelease a
WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
AND TO_DATE ('2008-7-2', 'yyyy-mm-dd')
GROUP BY a.fdepartment) aa,
(
select a.FDEPARTMENT,a.FRELEASECOUNT
from OA_DEPTRELEASE a
) bb,
(
select nvl(sum(a.FRELEASECOUNT),0) tot
from OA_DEPTRELEASE a
) cc
where aa.FDEPARTMENT=bb.FDEPARTMENT(+)
GROUP BY aa.fdepartment,nvl(bb.FRELEASECOUNT,0),cc.tot
注:
现在语句:cc.tot字段统计的是OA_DEPTRELEASE.FRELEASECOUNT全部数量。
正确结果应为:cc.tot字段统计 查询出的显示结果部门的FRELEASECOUNT字段的总和。
要贴表结构,数据,目标结果集
你的开发环境好像分析函数都用不了,否则不用写得这么复杂了,说真的,写到后面我都头晕了,写了二个你不能用,还非要用复杂的写法才能用
所以你把你几张表的结构,数据全要贴出来,现在出来的结果集,你想要的结果集,这样人家才能帮你看问题出在哪