请教多层嵌套SQL关连查询

Question

1.第一张表结构如下（T）：
deptname username pftype csgrade fggrade ldgrade    id
 综合处  郑处   2       0       5       0       125298558056255
 综合处  曹处   0  4       5       3       125532739039087
 综合处  徐处   1  2 4       5       125412832434300
 综合处  吕处   0  5 2       4       125402861307800
 综合处  吕处   2  5       4       0       125419001675031
 综合处  徐处   1  5 2       0       125531286904600
 综合处  曹处   1  3 3       5       125531409467146
 综合处  徐处   2  5 3       0       125532885317150
 办领导  朱处   1  5 3       0       125531630317103
 办领导  刘   1  5 5       5       125533155726500
2.第二张结构如下A：
id                     filename
125402861307800        测试A1
125532739039087        测试A2第三张（B）
id                     filename
125412832434300        测试B1
125531286904600        测试B2
125531409467146        测试B3
125531630317103        测试B4
125533155726500        测试B5第四张表结构（C）
id                     filename
125298558056255        测试C1
125419001675031        测试C2
125532885317150        测试C3单个文件评分标准：如果pftype =0或1
pf=(csgrade*0.3+ fggrade*0.3+ ldgrade*04) 
例如：测试A2  pf=( 4*0.3+5 *0.3+ 3 *0.4)=3.9;
 如果pftype =2
pf=(csgrade*0.5+ fggrade*0.5);例如：测试A2文件的pf=( 4*0.3+5 *0.3+ 3 *0.4);测试C2 pf=( 5*0.3+4*0.3)=4.5;
希望得到如下的结果：
         处室     姓名    文件数    平均分      明细(文件名 评分)
综合处 曹 2 3.85       测试A2     3.9
                                                测试B3     3.8 办领导 刘 1 5          测试B5     5 综合处 徐 3 3.3        测试B1     3.8
                                                测试B2     2.1
                                                测试C3     4 综合处 吕 2 4.1        测试A1     3.7
                                                测试C2     4.5 办领导 朱 1 2.4        测试B4     2.4
综合处 郑 1 2.5        测试C1     2.5

Answer 1

这是我的部分实现，没有实现我想要的结果： 
1.评分统计     
[align=left]select c.p_user_id,
                     c.p_dept_name,
                     c.p_user_name,
                     count(c.p_user_id) filenum,
                     avg(c.pf) pf
                from (select t.p_user_id,
                             t.p_dept_name,
                             t.p_user_name,
                             (t.cs_grade * 0.3 + t.fg_grade * 0.3 +
                             t.ld_grade * 0.4) pf
                        from t_oa_jzb_pf t
                       where t.pf_type = '0'
                          or t.pf_type = '1'
                      union
                      select t.p_user_id,
                             t.p_dept_name,
                             t.p_user_name,
                             (t.cs_grade * 0.5 + t.fg_grade * 0.5) pf
                        from t_oa_jzb_pf t
                       where t.pf_type = '2') c
               group by c.p_user_id, c.p_user_name, c.p_dept_name[/align]
2.评分统计明细
[align=left]
select insend.file_name filename,
   (p.cs_grade * 0.3 + p.fg_grade * 0.3 + p.ld_grade * 0.4) pf
   from t_oa_jzb_pf p, t_oa_jzb_insend_fileinfo insend
  where p.pf_type = 0
    and p.process_instance_id = insend.process_instance_id and p.p_user_id=208
    union
    select interior.file_name filename,
        (p.cs_grade * 0.3 + p.fg_grade * 0.3 + p.ld_grade * 0.4) pf
   from t_oa_jzb_pf p, t_oa_jzb_interior_fileinfo interior
  where p.pf_type = 1
    and p.process_instance_id = interior.process_instance_id and p.p_user_id=208
    union
     select briefing.file_name filename,
        (p.cs_grade * 0.5 + p.fg_grade * 0.5 ) pf
   from t_oa_jzb_pf p, t_oa_jzb_briefing_fileinfo briefing
  where p.pf_type = 2
    and p.process_instance_id = briefing.process_instance_id and p.p_user_id=208[/align]

Answer 2

select a.deptname "处室",
       a.username "姓名",
       count(id) over(partition by a.deptname,a.username) "文件数",
      (select sum((case when pftype = 2 then csgrade*0.5+ fggrade*0.5 
                   else csgrade*0.3+ fggrade*0.3+ ldgrade*04
                   end)
                 )/count(id)
       from T t where t.deptname = a.deptname and t.username = a.username) "平均分",
       b.filename "文件名",
      (case when pftype = 2 then csgrade*0.5+ fggrade*0.5 
            else csgrade*0.3+ fggrade*0.3+ ldgrade*04
       end) "评分"
from   T a,
      (select id, filename from A 
       union all
       select id, filename from B
       union all
       select id, filename from c
      ) b
where  a.id = b.id(+);

Answer 3

sum((case when pftype = 2 then csgrade*0.5+ fggrade*0.5 
                   else csgrade*0.3+ fggrade*0.3+ ldgrade*04
                   end)
                 )/count(id)
case when 用在这里可能不行，也可以换成decodesum(decode(pftype,2,csgrade*0.5+ fggrade*0.5,csgrade*0.3+ fggrade*0.3+ ldgrade*04))/count(id)

Answer 4

[align=left]
这是2楼的查询结果，这个结果也不对。
平均分和单个文件的分数都不可能超过5的分。记录数应该是分六条显示，每个人员只有一条记录。
1 办领导 刘 1 23 测试督办                       23
2 办领导 朱 1 2.4 测试评分200910122234    2.4
3 综合处 曹 2 18.25 测试督办155555            21.8
4 综合处 曹 2 18.25 测试评分发文20091012    14.7
5 综合处 吕 2 11.3 测试批注发文              18.1
6 综合处 吕 2 11.3 测试评分20090929            4.5
7 综合处 徐 3 9.3 测试评分20090928            21.8
8 综合处 徐 3 9.3 测试退回理由              2.1
9 综合处 徐 3 9.3 测试信息简报评分20091012    4
10 综合处 郑 1 2.5 1111                    2.5
[/align]

Answer 5

else csgrade*0.3+ fggrade*0.3+ ldgrade*0.4
04改成0.4

Answer 6

select decode(rn,1,deptname,null) "处室",
       decode(rn,1,username,null) "姓名",
       decode(rn,1,filecount,null) "文件数",
       decode(rn,1,avscore,null) "平均分",
       filename "文件名",
       score "评分"
from (
      select a.deptname ,
             a.username ,
             count(id) over(partition by a.deptname,a.username) filecount,
            (select sum((case when pftype = 2 then csgrade*0.5+ fggrade*0.5 
                         else csgrade*0.3+ fggrade*0.3+ ldgrade*0.4
                         end)
                       )/count(id)
             from T t where t.deptname = a.deptname and t.username = a.username) avscore ,
             b.filename ,
            (case when pftype = 2 then csgrade*0.5+ fggrade*0.5 
                  else csgrade*0.3+ fggrade*0.3+ ldgrade*0.4
             end) score
            row_number() over(partition by a.deptname,a.username order by b.filename) rn
      from   T a,
            (select id, filename from A 
             union all
             select id, filename from B
             union all
             select id, filename from c
            ) b
      where  a.id = b.id(+)
     );