大SQL完成的过程：工程项目收支及支出明细|关联两行整合到一行|

问题提出：高总给我提出了一个需求，得到各个公司各个项目支付情况，具体是每个项目客商支付单位和个人的比例，以此查看分公司资金归集和支付情况。

从上周四问题的提出，到周五，周六，今天可以说比较完善的解决。

首先看下问题的细节，itpub发帖http://www.itpub.net/thread-1772420-1-1.html

如图，这个表是一个select出来的结果，这里暂且重命名为表t
bdname有“客商辅助核算” （对应的是客商），“工程项目”（对应的是工程项目）
bdname为工程项目的有多个一样的名字“中国农业科学院附属小学抗震改造建设工程”，
我现在要条件valuecode='0102461101'(注意valuecode同时针对客商和项目)这个的时候，找出他们对应下面的两个bdname为客商辅助的客商（绿色剪头，这两个客商为这个项目提供材料）
说明1：这个项目和为其提供材料的客商对应同一个pk_voucher。
说明2：查询结果很多的项目和客商

我本来的是这样写的
  select * from t where bdname='客商辅助核算' --筛选出客商
  and pk_voucher in
(select pk_voucher from t where valuecode='0102461101')--子查询为同一个pk_voucher
可是报错说下面的t找不到。（with t as没有问题，公司不让用with t as，通过两个表关联查询也能解决，但是sql特别的长，可读性也不高）
请教高手，怎么写
最终的查询结果应该是这样的

直接先给出完善的sql：

select gcode,gname,valuecode,valuename,custcode,custname,pay,sum(pay)over(partition by valuecode) 总额,--三层：加上付款比例
trunc((pay/sum(pay)over(partition by valuecode))*100,2)比例,cc 次数 from(
 select c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,count(distinct c.pk_voucher)cc,sum(c.credit) pay from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'                    
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
    group by c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname )
    where pay<>0 --有调整分录，银行余额变成0
    group by gcode,gname,valuecode,valuename,custcode,custname,pay,cc
    order by gcode,valuecode,trunc((pay/sum(pay)over(partition by valuecode))*100,2) desc

第一层select利用了表的自连接，区分了两个select，第一个重命名为c，第二个为p，c得到的detail中辅助核算为客商的行，p得到的是deail中辅助位项目的行。

考虑到pk_voucher的唯一性（包括多账簿）p中的条件较之c少了很多。这一层也有一个条件and bd_glorgbook.glorgbookcode like '01%-0001'得到的是分公司核算账簿项目支付情况（公司也有-0003多账簿），否则第三层select 的“sum(pay)over(partition by valuecode) 总额”会不正确（因为0003帐簿也有项目valuecode）

这一层p表条件也是可以去掉，因为没有用到p的bd_glorgbook，但是不明白的是去掉之后查询时间变成，不去了

join bd_glorgbook

on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  

第二层select根据pk_voucher的关联，取了c的账簿信息，p的项目信息，c的客商信息，c的贷方发生额（c的客商辅助用在银行存款上），因为c的pk_voucher取到说明是银行付款了，p的pk_voucher取到说明是项目发生的，这样就能判断得到的数据是发生在项目上的客商支付。

这一层也有一个反应客商支付次数的字段“count(distinct c.pk_voucher)cc”，这里加上了distinct，为的是过滤一个凭证中一借多贷的情况，如下图

第三层select将每个项目支付的客商多笔进行汇总，并且加上了每个项目的支付总额和每个客商在总额的比例，总额是“sum(pay)over(partition by valuecode)”，这里如果用“sum(pay)over(order by valuecode)”会不正确，因为行中的pay会有相等的，使用partition根据项目编码分组问题解决。

结果如下

 

周五回顾：

oracle群cryking帮助下写的大sql（也是自连接），这个sql较之上面的sql效率低了很多，在我的笔记本上面，跑了2分钟还在分析，最终失败。

这里值得学习的有亮点：

1，使用count(distinct bd_bdinfo.bdname) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn>=2得到的是同时包含客商和项目辅助的凭证，然后a过滤得到的是其中的客商，b得到的是项目，然后pk_voucher关联

2，cryking的帮助下写了and (gl_detail.debitamount=0 OR bd_bdinfo.bdname<>'客商辅助核算')，这一个条件可以去除上面凭证中银行存款放生在借方的

 

select gc,gn,vp,vn, valuecode,valuename,/*bdname,*/ssd,ssc,sum(ssc)over(partition by vp) 总额,
trunc((ssc/sum(ssc)over(partition by vp))*100,2)比例, cc 次数 from (
select a.gc,a.gn, a.valuecode,a.valuename,a.bdname,sum(a.sd)ssd,sum(a.sc)ssc,count(a.valuecode)cc,b.valuecode vp,b.valuename vn from 
(
select gc,gn, ex,dispname,sum(debit)sd,sum(credit)sc,vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
  from (select count(distinct bd_bdinfo.bdname) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               bd_glorgbook.glorgbookcode gc,
           bd_glorgbook.glorgbookname gn, 
               gl_detail.explanation ex,
               bd_accsubj.dispname,
               gl_detail.debitamount debit,
               gl_detail.creditamount credit,
               gl_detail.prepareddatev vdate,
               gl_voucher.no,
               gl_voucher.pk_voucher,
               bd_bdinfo.bdname,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               gl_detail.pk_systemv,
               gl_detail.detailindex
          from bd_accsubj
          join gl_detail
            on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
          join bd_glorgbook
            on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.yearv in(/*'2011',*/ '2012')
           and bd_glorgbook.glorgbookcode like '01%-0001'
            and gl_detail.periodv<>'00'
            -- and (bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '4104%'
           and (gl_detail.debitamount=0 OR bd_bdinfo.bdname<>'客商辅助核算')
           and bd_bdinfo.bdname in('工程项目','客商辅助核算'))
 where rn >= 2  
 and bdname='客商辅助核算'
 group by gc,gn, ex,dispname,vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
 order by vdate, no
)a,

(select gc,gn, ex,dispname,sum(debit),sum(credit),vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
  from (select count(distinct bd_bdinfo.bdname) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
   bd_glorgbook.glorgbookcode gc,
           bd_glorgbook.glorgbookname gn, 
               gl_detail.explanation ex,
               bd_accsubj.dispname,
               gl_detail.debitamount debit,
               gl_detail.creditamount credit,
               gl_detail.prepareddatev vdate,
               gl_voucher.no,
               gl_voucher.pk_voucher,
               bd_bdinfo.bdname,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               gl_detail.pk_systemv,
               gl_detail.detailindex
          from bd_accsubj
          join gl_detail
            on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
          join bd_glorgbook
            on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.yearv in(/*'2011', */'2012')
            and bd_glorgbook.glorgbookcode like '01%-0001'
            and gl_detail.periodv<>'00'
            -- and (bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '4104%'
           and (gl_detail.debitamount=0 OR bd_bdinfo.bdname<>'客商辅助核算')
           and bd_bdinfo.bdname in('工程项目','客商辅助核算'))
 where rn >= 2  
 and bdname='工程项目'
 group by gc,gn,ex,dispname,vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
 order by vdate, no
) b
where a.pk_voucher=b.pk_voucher  --and b.valuecode='0102161001'
group by a.gc,a.gn, a.valuecode,a.valuename,a.bdname,b.valuecode,b.valuename order by length(a.valuename))
group by gc,gn,vp,vn,valuecode,valuename,bdname,ssc,ssd,cc order by gc,  vp,ssc/sum(ssc)over(order by bdname) desc

感谢一下“支出明细 原始.sql”

select ex,dispname,sum(debit),sum(credit),vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
  from (select count(distinct bd_bdinfo.bdname) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               gl_detail.explanation ex,
               bd_accsubj.dispname,
               gl_detail.debitamount debit,
               gl_detail.creditamount credit,
               gl_detail.prepareddatev vdate,
               gl_voucher.no,
               gl_voucher.pk_voucher,
               bd_bdinfo.bdname,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               gl_detail.pk_systemv,
               gl_detail.detailindex
          from bd_accsubj
          join gl_detail
            on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
          join bd_glorgbook
            on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.yearv = '2012'
           and bd_glorgbook.glorgbookcode = '010201-0001'
            and gl_detail.periodv<>'00'
            -- and (bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '4104%'
           and (gl_detail.debitamount=0 OR bd_bdinfo.bdname<>'客商辅助核算')
           and bd_bdinfo.bdname in('工程项目','客商辅助核算'))
 where rn >= 2  
 group by ex,dispname,vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
 order by vdate, no

 

项目的收入支出相对简单，可以根据gl_balance搞定（相当于IUFO的工程项目基本情况表了），收入支出可以用到case写到一块，如下

select bd_glorgbook.glorgbookcode,
           bd_glorgbook.glorgbookname,
           --gl_balance.year,
           gl_freevalue.valuecode,
           gl_freevalue.valuename,
           --gl_balance.assid,
           bd_bdinfo.bdname,
           sum(case when bd_accsubj.subjcode like '2123%' then gl_balance.creditamount else 0 end)收入,
           sum(case when bd_accsubj.subjcode like '4104%' then gl_balance.debitamount else 0 end) 支出         
          
      from gl_balance, bd_accsubj, bd_glorgbook,gl_freevalue,bd_bdinfo
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and   gl_balance.assid=gl_freevalue.freevalueid
       and gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
       --and bd_glorgbook.glorgbookcode='0100-0001'
       and ( bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '2123%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2011','2012')
       and bd_glorgbook.glorgbookcode like '01%-0001' 
       and bd_bdinfo.bdname='工程项目'
    group by ROLLUP((bd_glorgbook.glorgbookcode,
              bd_glorgbook.glorgbookname,bd_bdinfo.bdname,
             gl_freevalue.valuecode,gl_freevalue.valuename ))
    order by  bd_glorgbook.glorgbookcode  asc ,gl_freevalue.valuecode  asc 

最开始不懂用case，结果分开写

收入

select bd_glorgbook.glorgbookcode,
           bd_glorgbook.glorgbookname,
           gl_balance.year,
           gl_freevalue.valuecode,
           gl_freevalue.valuename,
           gl_balance.assid,
           bd_bdinfo.bdname,
           sum(gl_balance.debitamount),
           sum(gl_balance.creditamount)     
          
      from gl_balance, bd_accsubj, bd_glorgbook,gl_freevalue,bd_bdinfo
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and   gl_balance.assid=gl_freevalue.freevalueid
       and gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
       --and bd_glorgbook.glorgbookcode='0100-0001'
       and bd_accsubj.subjcode like '2123%'  
       and gl_balance.period<>'00'
       and gl_balance.year ='2012' 
       and bd_glorgbook.glorgbookcode = '010201-0001' 
       and bd_bdinfo.bdname='工程项目'
    group by ROLLUP((bd_glorgbook.glorgbookcode,
              bd_glorgbook.glorgbookname,bd_bdinfo.bdname,
              gl_balance.year,gl_balance.assid,gl_freevalue.valuecode,gl_freevalue.valuename ))
    order by  gl_balance.year  asc ,bd_glorgbook.glorgbookcode  asc 

 

支出

select ex,dispname,sum(debit),sum(credit),vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
  from (select count(distinct bd_bdinfo.bdname) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               gl_detail.explanation ex,
               bd_accsubj.dispname,
               gl_detail.debitamount debit,
               gl_detail.creditamount credit,
               gl_detail.prepareddatev vdate,
               gl_voucher.no,
               gl_voucher.pk_voucher,
               bd_bdinfo.bdname,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               gl_detail.pk_systemv,
               gl_detail.detailindex
          from bd_accsubj
          join gl_detail
            on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
          join bd_glorgbook
            on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.yearv = '2012'
           and bd_glorgbook.glorgbookcode = '010201-0001'
            and gl_detail.periodv<>'00'
            -- and (bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '4104%'
           and (gl_detail.debitamount=0 OR bd_bdinfo.bdname<>'客商辅助核算')
           and bd_bdinfo.bdname in('工程项目','客商辅助核算'))
 where rn >= 2  
 group by ex,dispname,vdate,no,pk_voucher,bdname,valuecode,valuename,pk_systemv
 order by vdate, no

 2013-03-16 23:25:22 更新：

想把项目收支和客商支付整合一起，根据项目编码作条件，如下

select pb.*,pd.总额,pd.custcode,pd.custname,pd.pay,pd.比例,pd.次数 from 

(select gcode,gname,valuecode,valuename,custcode,custname,pay,sum(pay)over(partition by valuecode) 总额,--三层：加上付款比例
trunc((pay/sum(pay)over(partition by valuecode))*100,2)比例,cc 次数 from(
 select c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,count(distinct c.pk_voucher)cc,sum(c.credit) pay from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'                    
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
    group by c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname )
    where pay<>0 --有调整分录，银行余额变成0
    group by gcode,gname,valuecode,valuename,custcode,custname,pay,cc
    order by gcode,valuecode,trunc((pay/sum(pay)over(partition by valuecode))*100,2) desc
) pd,
(
select bd_glorgbook.glorgbookcode,
           bd_glorgbook.glorgbookname,
           --gl_balance.year,
           gl_freevalue.valuecode,
           gl_freevalue.valuename,
           --gl_balance.assid,
           bd_bdinfo.bdname,
           sum(case when bd_accsubj.subjcode like '2123%' then gl_balance.creditamount else 0 end)收入,
           sum(case when bd_accsubj.subjcode like '4104%' then gl_balance.debitamount else 0 end) 支出         
          
      from gl_balance, bd_accsubj, bd_glorgbook,gl_freevalue,bd_bdinfo
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and   gl_balance.assid=gl_freevalue.freevalueid
       and gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
       --and bd_glorgbook.glorgbookcode='0100-0001'
       and ( bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '2123%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2011')
       and bd_glorgbook.glorgbookcode like '01%-0001' 
       and bd_bdinfo.bdname='工程项目'
    group by ROLLUP((bd_glorgbook.glorgbookcode,
              bd_glorgbook.glorgbookname,bd_bdinfo.bdname,
             gl_freevalue.valuecode,gl_freevalue.valuename ))
    order by  bd_glorgbook.glorgbookcode  asc ,gl_freevalue.valuecode  asc 
)pb
where pd.valuecode=pb.valuecode
   and pd.gcode=pb.glorgbookcode  
order by pb.glorgbookcode,pb.valuecode,pd.比例 desc

效果图

可是这个项目的收入明显有问题，不知道原因

 发现了，这个是正确的，这个取的2011年的，对照的是2011 和2012年

不过再增加一个条件and pd.gcode=pb.glorgbookcode

 2013-03-17 01:05:41 紧急更新：

在去和室友show的时候，发现了严重的错误

可是实际上，“南钢综合利用放散高炉、转炉煤气发电工程”这个项目支付“南京卓群金属材料有限公司”一共才两笔，

打开之后

这个错误的支付总额3700000=500000*3+550000*4 ，看后台

看得出条件 c.pk_voucher=p.pk_voucher ，系统会找在一个凭证里面客商和项目一一对应，如果有四个项目，那么客商支付也就四行同样的数据，明显重复，解决很简单，直接在原始的查询里面增加一个查询字段distinct(c.detailindex) ，这样保证了银行支付贷方的行数仅仅一行（或多行，但能保证不重复）

ps：这个distinct条件要写在前面，这个distinct(c.detailindex)表示凭证的行数，产生的数字会和其他的凭证行数有相同值，但是不会冲突，因为其他值不一样（select的字段决定）

但是像南京公司的第一个凭证，一个凭证里面两个工程施工，sql肯定不能判断支付的客商是那个项目的，只能两个都当做（可以考虑去除工程结算的项目辅助部分，比如第二个凭证），考虑工程施工科目都是借方发生，增加这个条件就行 and gl_detail.debitamount<>0 

这个sql是上面的第二次sql，原始数据查询，不分组

 
 select distinct(c.detailindex),c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,/*count(distinct c.pk_voucher)cc, */
 c.credit  pay ,c.pk_voucher from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode = '010601-0001'                    
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'
         -- and bd_glorgbook.glorgbookcode = '010201-0001'
           and gl_detail.debitamount<>0                     
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
  order by c.gcode,p.valuecode,c.custcode

然后依次更改上面的sql

2013-03-17 02:24:02 发现也是一个累人的活，主要是增加了distinct detailindex之后要单独一次select去除它，否则group 有问题

 select gcode,gname,valuecode,valuename,custcode,custname,pay,sum(pay)over(partition by valuecode) 总额,--三层：加上付款比例
trunc((pay/sum(pay)over(partition by valuecode))*100,2)比例,cc 次数  from
 (
 
 select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,sum(credit) pay  from 
 ( 
 select gcode,gname,valuecode,valuename,custcode,custname,credit,pk_voucher from 
(
  select distinct(c.detailindex),c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,c.credit,c.pk_voucher from--二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
          and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher
     order by c.gcode,p.valuecode,c.custcode))
     group by gcode,gname,valuecode,valuename,custcode,custname order by gcode,valuecode)
     where pay<>0
     group by gcode,gname,valuecode,valuename,custcode,custname,pay,cc
    order by gcode,valuecode,trunc((pay/sum(pay)over(partition by valuecode))*100,2) desc

 

 第二个 2 in1的，搞定睡觉

select pb.*,pd.总额,pd.custcode,pd.custname,pd.pay,pd.比例,pd.次数 from 

( select gcode,gname,valuecode,valuename,custcode,custname,pay,sum(pay)over(partition by valuecode) 总额,--三层：加上付款比例
trunc((pay/sum(pay)over(partition by valuecode))*100,2)比例,cc 次数  from
 (
 
 select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,sum(credit) pay  from 
 ( 
 select gcode,gname,valuecode,valuename,custcode,custname,credit,pk_voucher from 
(
  select distinct(c.detailindex),c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,c.credit,c.pk_voucher from--二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
          and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher
     order by c.gcode,p.valuecode,c.custcode))
     group by gcode,gname,valuecode,valuename,custcode,custname order by gcode,valuecode)
     where pay<>0
     group by gcode,gname,valuecode,valuename,custcode,custname,pay,cc
    order by gcode,valuecode,trunc((pay/sum(pay)over(partition by valuecode))*100,2) desc) pd,
(
select bd_glorgbook.glorgbookcode,
           bd_glorgbook.glorgbookname,
           --gl_balance.year,
           gl_freevalue.valuecode,
           gl_freevalue.valuename,
           --gl_balance.assid,
           bd_bdinfo.bdname,
           sum(case when bd_accsubj.subjcode like '2123%' then gl_balance.creditamount else 0 end)收入,
           sum(case when bd_accsubj.subjcode like '4104%' then gl_balance.debitamount else 0 end) 支出         
          
      from gl_balance, bd_accsubj, bd_glorgbook,gl_freevalue,bd_bdinfo
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and   gl_balance.assid=gl_freevalue.freevalueid
       and gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
       --and bd_glorgbook.glorgbookcode='0100-0001'
       and ( bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '2123%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2011')
       and bd_glorgbook.glorgbookcode like '01%-0001' 
       and bd_bdinfo.bdname='工程项目'
    group by ROLLUP((bd_glorgbook.glorgbookcode,
              bd_glorgbook.glorgbookname,bd_bdinfo.bdname,
             gl_freevalue.valuecode,gl_freevalue.valuename ))
    order by  bd_glorgbook.glorgbookcode  asc ,gl_freevalue.valuecode  asc 
)pb
where pd.valuecode=pb.valuecode
   and pd.gcode=pb.glorgbookcode  
order by pb.glorgbookcode,pb.valuecode,pd.比例 desc

 2013-03-17 14:13:36 更新：

多借多待（一个凭证多个工程施工，多个银行存款）的情况，这个sql不能判断那个客商对应那个工程项目，因此查询结果只能保证80%正确，for reference only

但是一借多贷没有问题，不可以多借一贷，多借多待（银行存款的客商不知道付给那个项目了）

下面的sql可以查询出多借（多个工程施工，并且贷方有银行存款）的会计分录

 

 select * from (
 select t.*, count(valuecode)over(partition by prepareddatev,no) rn from (
 select gcode,gname,prepareddatev,no,valuecode,valuename/*,custcode,custname,pay*/,pk_voucher
 from(
 select distinct(c.detailindex),c.gcode,c.gname,c.prepareddatev,c.no,p.valuecode,p.valuename,c.custcode,c.custname,/*count(distinct c.pk_voucher)cc, */
 c.credit  pay ,p.pk_voucher from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,gl_detail.yearv,gl_detail.prepareddatev,gl_voucher.no,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode = '010601-0001'                    
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'
           --and bd_glorgbook.glorgbookcode = '010601-0001'                    
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
    and c.yearv = '2011'
      and c.gcode = '010601-0001'   
  order by c.gcode,c.prepareddatev,c.no)
 group by  gcode,gname,prepareddatev,no,pk_voucher,valuecode,valuename
/* having count(distinct valuecode)>=2*/
 )t 
 )
where rn>=2 order by gcode,prepareddatev,no

  用到了count(valuecode)over(partition by prepareddatev,no)，如下图

 

简化版南京公司一个凭证中多个工程施工

 

select * from 
 (
 select gcode,gname,prepareddatev,no,valuecode,valuename/*,custcode,custname,pay*/,pk_voucher,
 count(distinct valuecode)over(partition by prepareddatev,no) rn 
 from(
 select distinct(c.detailindex),c.gcode,c.gname,c.prepareddatev,c.no,p.valuecode,p.valuename,c.custcode,c.custname,/*count(distinct c.pk_voucher)cc, */
 c.credit  pay ,p.pk_voucher from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,gl_detail.yearv,gl_detail.prepareddatev,gl_voucher.no,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
                              
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
                            
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
    and c.yearv = '2011'
      and c.gcode = '010601-0001'))
  where rn>=2
 group by  gcode,gname,prepareddatev,no,pk_voucher,valuecode,valuename,rn 
 order by gcode,prepareddatev,no

如果不取到工程项目，可以直接用having，并且这里用到了distinct valuecode，但是valuecode这个时候不可以作为字段，不可以后面分组（不明白为啥不能加上valuecode呢，现在大体知道是这个取到仅仅一行，可是valuecode是两个，只有使用partition）

 select gcode,gname,prepareddatev,no,/*valuecode,valuename,*//*custcode,custname,pay,*/pk_voucher
 from(
 select distinct(c.detailindex),c.gcode,c.gname,c.prepareddatev,c.no,p.valuecode,p.valuename,c.custcode,c.custname,/*count(distinct c.pk_voucher)cc, */
 c.credit  pay ,p.pk_voucher from --二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,gl_detail.yearv,gl_detail.prepareddatev,gl_voucher.no,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode = '010601-0001'                    
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'
           --and bd_glorgbook.glorgbookcode = '010601-0001'                    
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher 
    and c.yearv = '2011'
      and c.gcode = '010601-0001'   
  order by c.gcode,c.prepareddatev,c.no)
 group by  gcode,gname,prepareddatev,no,pk_voucher/*,valuename,valuecode*/
 having count(distinct valuecode)>=2
 order by gcode,prepareddatev,no

 更新：取工程施工借方发生额还是银行存款贷方发生额

给出两个 c为银行存款贷方，p为工程施工借方

select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c,--三层：加上付款比例
trunc((payc/sum(payc)over(partition by  gcode,valuecode))*100,2)比例c,payp,sum(payp)over(partition by  gcode,valuecode) 总额p,
trunc((payp/sum(payp)over(partition by  gcode,valuecode))*100,2)比例p,
cc 次数  from
 (
 
 select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,sum(ccredit) payc,sum(pdebit) payp  from 
 ( 
 select gcode,gname,valuecode,valuename,custcode,custname,ccredit,pdebit,pk_voucher from 
(
  select distinct(c.detailindex),c.gcode,c.gname,p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,c.pk_voucher from--二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
          and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher
     order by c.gcode,p.valuecode,c.custcode))
     group by gcode,gname,valuecode,valuename,custcode,custname order by gcode,valuecode)
     where (payc<>0 and payp<>0)
     group by gcode,gname,valuecode,valuename,custcode,custname,payc,payp,cc
    order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

刚刚看到了上海的一个分录，贷方有内部结算存款（刚刚又吓一跳，这两个南昌市青云谱区客商太像了，我又以为写的sql不可以处理银行存款客商重复多行的，应该ok）。

上海的这个情况明显的取工程施工不合适，因该取银行存款（统一吧用这个，并且查询的是项目支付客商的情况，主体应该是客商）

多借一贷取工程施工p作为客商支付额较为合适

实际还有下面的情况

下一步的研究方向：

一借一贷：付款取贷方的客商credit

一借多贷：付款取贷方的客商credit

多借一贷：付款取借方的工程debit

多借多贷：没有法子

待验证

select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c,--三层：加上付款比例
trunc((payc/sum(payc)over(partition by  gcode,valuecode))*100,2)比例c,cc 次数  from
 (
 
 select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
 sum(case when rn>=2 then pdebit else ccredit end) payc  from 
 ( 
 select gcode,gname,valuecode,valuename,custcode,custname,ccredit,pdebit,pk_voucher,
  count(distinct valuecode)over(partition by prepareddatev,no) rn  from 
(
  select distinct(c.detailindex),c.gcode,c.gname,c.prepareddatev,c.no,p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,c.pk_voucher from--二层：客商合并
  ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,  --一层：原始数据
            gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode like '01%-0001'   --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
          and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher
     order by c.gcode,p.valuecode,c.custcode))
     group by gcode,gname,valuecode,valuename,custcode,custname order by gcode,valuecode)
     where  payc<>0  
     group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc
    order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

 2013-03-18 10:24:30 紧急更新

周六晚上发现客商pay多陪使用distinct(c.detailindex)得以解决，可是字段增加了一个工程施工借方付款payp之后，distinct(c.detailindex)失效。

初步解决方案：

第二层select 作一个count(distinct p.pdebit )over(partition by c.prepareddatev,c.no ) re,然后增加一层select 对(case when re>=2 then 0 else pdebit end) pdebit2
测试通过了上面南京公司的凭证

测试通过了下面北京公司的多借一贷情况

但是没有解决，一个凭证同一个项目不同行的问题，像这样的应该取工程施工，re不能为2，否则外面一层就会把它变成0

 

 count(distinct p.detailindex )over(partition by c.prepareddatev,c.no ) re, --计算出一个凭证同一个工程项目多个的
     count(distinct  p.valuecode )over(partition by c.prepareddatev,c.no ) re2,--计算不出一个凭证多个工程项目

final2 目前南京的崔勇-农行有问题

2013-03-18 18:14:31 终于发现了问题，原来在partition的时候，崔勇-农行应该其他公司也有，rn变成了2 ，rn partition增加一个分组就行了：

count(distinct valuecode)over(partition by gcode, prepareddatev,no) rn

 select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c,--三层：加上付款比例
trunc((payc/sum(payc)over(partition by  gcode,valuecode))*100,2)比例c,cc 次数  from
   (select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
 sum(case when rn>=2 then pdebit2 else ccredit end) payc  from 
  (select gcode,gname,valuecode,valuename,custcode,custname,ccredit,pdebit2,pk_voucher,
  count(distinct valuecode)over(partition by gcode,prepareddatev,no) rn  from 
   (select   distinct(detailindex), gcode, gname, prepareddatev, no,
    valuecode, valuename, custcode, custname, ccredit, (case when re>=2 then 0 else pdebit end) pdebit2 ,   pk_voucher from   

 ( select  c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
     p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
     count(distinct p.detailindex )over(partition by c.prepareddatev,c.no ) re,c.pk_voucher  from--二层：客商合并
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,  --一层：原始数据
            gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
            bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2012'
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select  gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
          and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher )))
    group by gcode,gname,valuecode,valuename,custcode,custname order by gcode,valuecode)
      where  payc<>0   
     group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc
    order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

 

 这种多个借方工程项目统计的时候算支付个数，但是金额变成了0：

1,count(distinct p.detailindex )over(partition by c.prepareddatev,c.no ) re>=2

2,(case when re>=2 then 0 else pdebit end)结果借方取数变成了0

3, count(distinct valuecode)over(partition by gcode,prepareddatev,no) rn>=2

4, sum(case when rn>=2 then pdebit2 else ccredit end) payc 取借方（见2），合计为0

突然发现这样的话我多借一贷取借方已经费了（见上面的4），需要继续改正

 

 2013-03-18 23:07:24 多项目一银行存款问题解决，sql并且精简了

但是多借（不同项目）多贷（即使贷方同一个客商）问题没有解决，导致付款加倍（同一项目多借ok）

 
 
 select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c,--三层：加上付款比例
       trunc((payc/sum(payc)over(partition by  gcode,valuecode))*100,2)比例c,cc 次数  from
 
 (select gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit end) payc  from    --多项目付款取借方工程施工
 
(select  distinct(detailindex) , gcode, gname, prepareddatev, no,
                  valuecode, valuename, custcode, custname, ccredit,
                   (case when re>=2 then pdebit else 0 end) pdebit2,pk_voucher,re from   --多项目保留借方
 (select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.prepareddatev,c.no ) re,c.pk_voucher  from --re得到一个凭证中多个不同项目
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,  --一层：原始数据客商
            gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
            bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2013'
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher唯一性，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
           and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher ))
    group by gcode,gname,valuecode,valuename,custcode,custname)
    group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc
            order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

 

 更新sql解决多借多贷（借方随便，贷方同一客商）

count(distinct c.detailindex)over(partition by c.prepareddatev,c.no ) rc实现
经过测试，同一个项目多借多贷（同一个客商）ok

 
select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c,--三层：加上付款比例
       trunc((payc/sum(payc)over(partition by  gcode,valuecode))*100,2)比例c,cc 次数  from
 
 ((select  gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit2 end) payc  from    --多项目付款取借方工程施工
 (select distinct(gcode),gname,valuecode,valuename,custcode,custname,ccredit2,pdebit2,pk_voucher,re from(
 
(select  distinct(detailindex) , gcode, gname, prepareddatev, no,
                  valuecode, valuename, custcode, custname, 
        (case when re>=2 and rc>=2 then 0 else ccredit end) ccredit2,
     (case when re>=2 then pdebit else 0 end) pdebit2,pk_voucher,re from   --多项目保留借方
 (select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.prepareddatev,c.no ) re,
       count(distinct c.detailindex)over(partition by c.prepareddatev,c.no ) rc,--多个贷方银行存款
       c.pk_voucher  from --re得到一个凭证中多个不同项目
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,  --一层：原始数据客商
            gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
            bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2013'
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
         --and gl_detail.yearv = '2012'               --依靠pk_voucher唯一性，可以不要下面的条件
         -- and bd_glorgbook.glorgbookcode = '010201-0001'   
           and gl_detail.debitamount<>0                  
           and bd_bdinfo.bdname='工程项目' )  p 
    where c.pk_voucher=p.pk_voucher ))))
    
    
    group by gcode,gname,valuecode,valuename,custcode,custname))
    group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc
            order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc  

 2013-03-19 10:02:31 更新：

partition的学习

不正常：

见上图被禁公司2011年1月154号凭证 0102550901广外危改A区项目

这里如果条件where   valuecode='0102550901' and  custcode='010201' 写在了右边括号里，那么re rc取值都为1，如果写在外面就正常（仔细体会）

这样就解释了昨天为什么    count(distinct c.custcode)over(partition by c.gcode, c.prepareddatev,c.no ) rc不起效果

悲剧，这样的工程施工冲减取到的是贷方银行存款为0了

2013-03-19 12:45:46 更新：

下面的sql终于考虑的比较完善了，对于多借（不同项目）多贷（不同客商）取值为贷方客商/项目数 平均分配

测试发现payc还是有0的，第五层需要增加这个条件

    --第五层：增加比例
  select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c, 
       trunc((payc/sum(payc)over(partition by gcode,valuecode))*100,2)比例c,cc 次数  from
     
     -- 第四层：按照项目客商汇总（仅 rc>=2多项目时候取借方）
   ( select  gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit end) payc  from 


     --第三层：对多借或多贷取付款发生额
      (select  distinct (detailindex), gcode, gname, prepareddatev,no, valuecode, valuename, custcode, custname ,pk_voucher,--distinct去除借方重复
          ccredit,--除去多项目(见上层select)，一直取银行贷方
        (case when re>=2 and rc>=2 then ccredit/re when re>=2 then pdebit else 0 end)
         pdebit2,re,rc from   --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方)

      --第二层：分别取数并整合
  ( select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.gcode,c.prepareddatev,c.no ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.gcode, c.prepareddatev,c.no ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk_voucher  from 
       
        ---第一层：原始数据
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,   
           gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
           gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2012'             --年份
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
           left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
            and gl_detail.debitamount<>0  
         --and gl_detail.yearv = '2012'                 --依靠pk_voucher唯一性，可以不要的条件
            and bd_bdinfo.bdname='工程项目' ) p --第一层            
   where c.pk_voucher=p.pk_voucher ) --第二层
                                  )   --第三层        
     group by gcode,gname,valuecode,valuename,custcode,custname)  --第四层
   where payc<>0
   group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc --第五层
   order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc  
      

 加上每个项目的收入、支出（交给高，定稿？final？）

 

select pb.*,pd.总额c,pd.custcode,pd.custname,pd.payc,pd.比例c,pd.次数 from 

(  --第五层：增加比例
  select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c, 
       trunc((payc/sum(payc)over(partition by gcode,valuecode))*100,2)比例c,cc 次数  from
     
     -- 第四层：按照项目客商汇总（仅 rc>=2多项目时候取借方）
   ( select  gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit end) payc  from 


     --第三层：对多借或多贷取付款发生额
      (select  distinct (detailindex), gcode, gname, prepareddatev,no, valuecode, valuename, custcode, custname ,pk_voucher,--distinct去除借方重复
          ccredit,--除去多项目(见上层select)，一直取银行贷方
        (case when re>=2 and rc>=2 then ccredit/re when re>=2 then pdebit else 0 end)
         pdebit2,re,rc from   --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方)

      --第二层：分别取数并整合
  ( select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.gcode,c.prepareddatev,c.no ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.gcode, c.prepareddatev,c.no ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk_voucher  from 
       
        ---第一层：原始数据
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,   
           gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
           gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv  in('2011','2012')             --年份
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
           left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
            and gl_detail.debitamount<>0  
         --and gl_detail.yearv = '2012'                 --依靠pk_voucher唯一性，可以不要的条件
            and bd_bdinfo.bdname='工程项目' ) p --第一层            
   where c.pk_voucher=p.pk_voucher ) --第二层
                                  )   --第三层        
     group by gcode,gname,valuecode,valuename,custcode,custname)  --第四层
   where payc<>0
   group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc --第五层
   order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc  
      
) pd,
(
select bd_glorgbook.glorgbookcode,
           bd_glorgbook.glorgbookname,
           --gl_balance.year,
           gl_freevalue.valuecode,
           gl_freevalue.valuename,
           --gl_balance.assid,
           bd_bdinfo.bdname,
           sum(case when bd_accsubj.subjcode like '2123%' then gl_balance.creditamount else 0 end)收入,
           sum(case when bd_accsubj.subjcode like '4104%' then gl_balance.debitamount else 0 end) 支出         
          
      from gl_balance, bd_accsubj, bd_glorgbook,gl_freevalue,bd_bdinfo
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and   gl_balance.assid=gl_freevalue.freevalueid
       and gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
       --and bd_glorgbook.glorgbookcode='0100-0001'
       and ( bd_accsubj.subjcode like '4104%' or bd_accsubj.subjcode like '2123%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2011','2012')
       and bd_glorgbook.glorgbookcode like '01%-0001' 
       and bd_bdinfo.bdname='工程项目'
    group by ROLLUP((bd_glorgbook.glorgbookcode,
              bd_glorgbook.glorgbookname,bd_bdinfo.bdname,
             gl_freevalue.valuecode,gl_freevalue.valuename ))
    order by  bd_glorgbook.glorgbookcode  asc ,gl_freevalue.valuecode  asc 
)pb
where pd.valuecode=pb.valuecode
   and pd.gcode=pb.glorgbookcode  
order by pb.glorgbookcode,pb.valuecode,pd.比例c desc

 2013-03-20 16:41:46 update：继续学习partition

上面的select查询的数据

现在想做一个不同分公司对同一客商支付（itpub也有发帖）

第一思路

count(distinct custcode)over(partition by gcode)rn ，太多了应该不对（改正了valuecode变成custcode）

 

第二思路：查看了itpub当时给的（ count(bd_corp.unitname) over(partition by bd_cubasdoc.custname) cnt2）

count(distinct gcode)over(partition by valuename)rn

好像是对的

但是这里不能再精简条件 rn>=2，也就是说不能

select gcode,gname,custcode,custname,count(distinct gcode)over(partition by valuename)rn from xx  where

count(distinct gcode)over(partition by valuename)>=2  (也不可以直接使用重命名的rn>=2)

     

必须再外套select（2012年居然没有不同分公司给同一客商支付的）

sql简写了

 select * from
 (select gcode,gname,custcode,custname,count(  distinct  gcode)over(partition by valuename)rn from
xx )
   where rn>=2 order by  custcode

⊙﹏⊙b汗，搞成了valuename，项目了，应该是客商名称custname ，我说怎么感觉数据不正常

为什么会有北京公司和北京长兴永业貌似公司重复的记录呢，另外这里的rn什么意思？不是distinct gcode吗

答：应该是因为这个客商确实有其他公司支付了，然后就出现了，至于北京公司为什么有多行，是因为它本来在北京就有多行

先不深究，distinct一下

 2013-03-21 14:56:01 小更新：

对金额进行数字格式化处理：to_char(pd.总额c,'999,999,999.99')

这里必须要to_char一下，不加上不行，使用to_number也不行

oracle群里影哥今天写了一下我这个两行变一行，可是我发现还是自连接

WITH w1 AS(SELECT ID,rname,row_number()over(partition by id order by decode(NAME,'项目',0,1),rname) rn FROM xl),
     a  AS(SELECT * FROM w1 WHERE rn=1),
     b  AS(SELECT * FROM w1 WHERE rn>1)
SELECT a.id,a.rname "项目",
       b.rname "客商"
  FROM a,b
 WHERE a.id=b.id(+)

 17:29:31 更新：http://www.itpub.net/forum.php?mod=viewthread&tid=1774331&page=1#pid21192110

发帖到itpub总有惊喜：

 

  select max(decode(bdname,'工程项目',custcode))pc,max(decode(bdname,'工程项目',custname))pn,
         max(decode(bdname,'客商辅助核算',custcode))cc,max(decode(bdname,'客商辅助核算',custname))cn from

  (select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname, gl_detail.prepareddatev,gl_voucher.no,
   gl_detail.debitamount debit, --一层：原始数据
            gl_detail.creditamount credit,  gl_voucher.pk_voucher,
               bd_bdinfo.bdname, gl_freevalue.valuecode custcode,gl_freevalue.valuename custname
               from  
   gl_detail
     
           
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2011'
           and bd_glorgbook.glorgbookcode= '010201-0001'
           and bd_bdinfo.bdname in('工程项目','客商辅助核算')
           and gl_detail.prepareddatev= '2011-01-04'
          
           order by   gl_detail.prepareddatev,gl_voucher.no ) group by pk_voucher

2013-03-22 08:51:04 更新  itpub相关

发帖到了itpub oracle开发区，得到了dingjun123，newkid，demonat的跟帖

这里发上他们的头像，表示感激

特别是版主newid，他说“截图只是表明了你的预期结果，还需要CREATE TABLE语句，INSERT语句。数据不用很多能说明问题就行。你还是没有看提问的艺术贴。为了尽快得到解答，你得提供构造测试环境的脚本。”

虽然刚开始，我稍微有一点点反感和急躁，但是我简单的一想，事情就应该这样做，不应该让跟帖的人自己写建表的环境吧。

在昨天发帖到itpub之后，两位给了max decode行转列，但是激动之余到了住处测试，发现了下面的问题

但是结果还是有问题，就是同一个pk里面我会有一个项目给三个客商付款（或者一个客商三个不同的金额）的，如下图

 
 
可是我max decode之后只取了一个最大的，明显的不合理，需要三个都取到，下面是newkid的帮助过程

首先我按照版主newid写了环境代码（折腾死我了，本来是用create table，可是insert into values总不成功，说精度不够）

 

with w1 as(select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
           select 'c01' valuecode,'北京京开文京五金经销部' valuename,'客商' bdname,1000 pay, 'pk_01' pk,2 dindex   from dual
             union all
          select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
             select 'c01' valuecode,'北京京开文京五金经销部' valuename,'客商' bdname,5000 pay, 'pk_01' pk,3 dindex   from dual
             union all
             select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
             select 'c02' valuecode,'北京昌源武豪贸易中心' valuename,'客商' bdname,2000 pay, 'pk_01' pk,4 dindex   from dual
                )
    select * from w1

 

加上row_number

select w1.*, ROW_NUMBER() OVER(PARTITION BY bdname,pk ORDER BY dindex) rn from w1

执行newkid的代码

with w1 as(select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
           select 'c01' valuecode,'北京京开文京五金经销部' valuename,'客商' bdname,1000 pay, 'pk_01' pk,2 dindex   from dual
             union all
          select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
             select 'c01' valuecode,'北京京开文京五金经销部' valuename,'客商' bdname,5000 pay, 'pk_01' pk,3 dindex   from dual
             union all
             select 'p01' valuecode,'太阳宫5-7#公寓楼工程' valuename,'项目' bdname,8000 pay, 'pk_01' pk,1 dindex   from dual
             union all
             select 'c02' valuecode,'北京昌源武豪贸易中心' valuename,'客商' bdname,2000 pay, 'pk_01' pk,4 dindex   from dual
                )
SELECT pk,
      max(decode(bdname, '项目', valuecode)) pcode,
       max(decode(bdname, '项目', valuename)) pname,
       max(decode(bdname, '客商', valuecode)) ccode,
       max(decode(bdname, '客商', valuename)) cname,
       max(decode(bdname, '客商', pay)) payc,
       max(decode(bdname, '项目', pay)) payd

  FROM (
select w1.*,ROW_NUMBER() OVER(PARTITION BY bdname,pk ORDER BY dindex) rn
from  w1 )
GROUP BY pk,rn;

对于rn，目前处于理解中

上面是处理一项目多客商的情况，通过，可是如果多项目一客商呢（包括同一客商不同行），没有想到也通过

刚刚我减少了一行项目

然后发现了问题

可是仔细思考了一下，这样的情况应该不会出现，现实的凭证后台查询中客商和项目是能对应的，一对多或者多对一或者多对多，总之，两行之间能对应

继续对rn理解

下面是先不max下的rn分布

max下面增加rn

 目前终于发现了问题，对于这样的情况（一个项目多个客商，并且项目就一行）

结果会有null

oracle群里问辰影，他使用了DENSE_RANK()，首先看看这个和row_number的区别

dense_rank()会对同一等级的相同表示（这里的1）

     select 'c03' valuecode,'2北京昌源武豪贸易中心' valuename,'客商' bdname,22000 pay, 'pk_02' pk,8 dindex   from dual),
     w2 AS(SELECT w1.*,DENSE_RANK()OVER(PARTITION BY pk ORDER BY bdname) rn FROM w1)
SELECT x.pk,x.pname,x.p_pay,x.cname,x.c_pay FROM(
SELECT pk,max(valuename)over(PARTITION BY pk) pname,max(pay)over(PARTITION BY pk) p_pay,
       min(valuename)over(PARTITION BY pk,valuename,pay) cname,min(pay)over(PARTITION BY pk,valuename,pay) c_pay,
       rn
  FROM w2) x
 WHERE rn<>2 

结果虽然可以，但是max 和min都用了，不能规律性了，实际验证是虽然是规律，因为1和2可以判断项目和客商，但是不能解决一个pk下多项目单客商

 

 2013-03-24 17:57:22 小更新

下面的sql能够实现统计同时含有“工程项目、客商辅助核算”的凭证

方法是：

1， and ((bd_bdinfo.bdname='工程项目' and gl_detail.debitamount>0 )or (bd_bdinfo.bdname='客商辅助核算' and gl_detail.creditamount>0 ))

首先找到具有工程项目（并且发生额借方>0）或者客商辅助的（并且贷方>0）

2，外面一层count(distinct bdname)over (partition by gl_voucher.pk_voucher)r1=2的确保同时含有两者

可是下面不好办了，不自连接不好客商项目一行

 
  select * from (
 select  bd_glorgbook.glorgbookcode,
     gl_detail.prepareddatev pdate ,
       gl_voucher.no  ,
       gl_detail.debitamount debit,
       gl_detail.creditamount credit,      
        bd_bdinfo.bdname ,
        gl_freevalue.valuecode ,
        gl_freevalue.valuename ,
        count(distinct bdname)over (partition by  gl_voucher.pk_voucher)r1,        
        gl_voucher.pk_voucher,
       gl_detail.detailindex
  from   gl_detail     
  join bd_glorgbook
    on bd_glorgbook.pk_glorgbook =gl_detail.pk_glorgbook
  join gl_voucher
    on gl_detail.pk_voucher = gl_voucher.pk_voucher
  left join gl_freevalue
    on gl_detail.assid = gl_freevalue.freevalueid
  left join  bd_bdinfo
    on gl_freevalue.checktype=bd_bdinfo.pk_bdinfo
 where gl_detail.dr = '0'
 and gl_detail.periodv<>'00'
   and gl_detail.yearv= '2012'
   and bd_glorgbook.glorgbookcode = '010201-0001'
  and ((bd_bdinfo.bdname='工程项目' and gl_detail.debitamount>0 )or (bd_bdinfo.bdname='客商辅助核算' and gl_detail.creditamount>0 )))
  where r1=2   
 order by pdate, no 

 2013-03-24 23:54:15 更新

itpub发帖咨询统计凭证中包含项目和客商辅助，版主bell6248给出解答

这里用的是when count(distinct decode(bdname, '工程项目', 1, '客商辅助核算', 2, null)) over(partition by pk) = 2

其中decode统计的是工程项目（1）和客商辅助核算（2）的类别，因此这里的1 2 可以是任意数

但是得到的凭证仍然有其他辅助，还是上面的sql好，不过这个case when和decode用的太好了

with t as (select ''bdname,'pk1'pk,1 dindex from dual
            union all
          select ''bdname,'pk1'pk,2 dindex from dual  
      union all
          select '工程项目'bdname,'pk2'pk,1 dindex from dual  
      union all
          select '客商辅助核算'bdname,'pk2'pk,2 dindex from dual  
          union all
          select '部门档案'bdname,'pk2'pk,2 dindex from dual  
          union all
          select '客商辅助核算'bdname,'pk2'pk,2 dindex from dual 
          union all 
            select '工程项目'bdname,'pk3'pk,1 dindex from dual  
      union all
        select '部门档案'bdname,'pk3'pk,2 dindex from dual  
   )
select bdname,
             pk,
             dindex,
             case
                when count(distinct decode(bdname, '工程项目', 1, '客商辅助核算', 2, null)) over(partition by pk) = 2 then
                            1
                  else
                             0
              end m
  from t

2013-03-25 11:07:14

newkid版主还问我要着feedback，我早上花费了两个小时完成，这里很恐怖的发现多项目一客商（不同行）取数有问题，本来是不用对ccredit case的，下面必须要有，要验证上面的sql了

with w1 as(   /*总体说明：同一pk下反应此项目给其客商支付款的情况，其中payp表示以项目角度取数，payc以客商角度取数，
              同一pk下项目和客商在不同行（bdname能够说明此行是项目还是客商），要写sql将同一pk下项目和客商整理成一行，付款因情况不同取数有可能是payp或者payc等，详细见下*/
          
             --情况1：一项目（一行）一客商（一行），付款取客商payc
          select 'pk01' pk, 'p01' valuecode,'项目01' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual
             union all
           select 'pk01' pk,'c01' valuecode,'客商01' valuename,'客商' bdname,0 payp,1000 payc, 2 dindex   from dual
         
          --情况2：一项目（多行）一客商（一行），付款取客商payc           
             union all             
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,4000 payp, 0 payc,1 dindex   from dual             
             union all
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,3000 payp, 0 payc,2 dindex   from dual
             union all 
             select 'pk02' pk,'c02' valuecode,'客商02' valuename,'客商' bdname,0 payp, 7000 payc,3 dindex   from dual
             
           --情况3：一项目（一行）多客商，付款取客商各自payc 
                
            union all             
          select 'pk03' pk,'p03' valuecode,'项目03' valuename,'项目' bdname,4600 payp, 0 payc,1 dindex   from dual             
             union all
          select 'pk03' pk,'c03' valuecode,'客商03' valuename,'客商' bdname,0 payp, 3500 payc,2 dindex   from dual
             union all 
             select 'pk03' pk, 'c04' valuecode,'客商04' valuename,'客商' bdname,0 payp, 1100 payc,3 dindex   from dual
             
            --情况4：一项目（多行）多客商，付款取客商各自payc  
            
              union all             
          select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1600 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1900 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk04' pk,'c05' valuecode,'客商05' valuename,'客商' bdname,0 payp, 500 payc,3 dindex   from dual
             union all 
             select 'pk04' pk, 'c06' valuecode,'客商06' valuename,'客商' bdname,0 payp, 3000 payc,4 dindex   from dual
             
           --情况5：多项目一客商（一行），付款取项目各自payp
              
              union all             
          select 'pk05' pk,'p05' valuecode,'项目05' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk05' pk,'p06' valuecode,'项目06' valuename,'项目' bdname,2900 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk05' pk,'c07' valuecode,'客商07' valuename,'客商' bdname,0 payp, 3900 payc,3 dindex   from dual
          
          --情况6：多项目一客商（多行），付款取项目各自payp
              
              union all             
          select 'pk06' pk,'p07' valuecode,'项目07' valuename,'项目' bdname,1100 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk06' pk,'p08' valuecode,'项目08' valuename,'项目' bdname,2600 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 900 payc,3 dindex   from dual
          union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 2800 payc,4 dindex   from dual
          
           --情况7：多项目多客商，付款取各个客商payc/项目数量（这个情况没有法子，只好让每个项目平摊付款数，表示客商平均给项目提供材料）
              
              union all             
          select 'pk07' pk,'p09' valuecode,'项目09' valuename,'项目' bdname,9000 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk07' pk,'p10' valuecode,'项目10' valuename,'项目' bdname,1600 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk07' pk,'c09' valuecode,'客商09' valuename,'客商' bdname,0 payp, 3800 payc,3 dindex   from dual
          union all   
          select 'pk07' pk,'c10' valuecode,'客商10' valuename,'客商' bdname,0 payp, 6800 payc,4 dindex   from dual
          
                )
                
select   valuecode,valuename,custcode,custname, 
          sum(case when re>=2 then payp2 else payc2 end) pay  from 


     --第三层：对多借或多贷取付款发生额
      (select  distinct   valuecode, valuename, custcode, custname ,pk,--distinct去除借方重复
          (case when re>=2 then 0 else payc end) payc2,--除去多项目(见上层select)，一直取银行贷方
        (case when re>=2 and rc>=2 then payc/re when re>=2 then payp else 0 end)
         payp2,re,rc from   --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方)

      --第二层：分别取数并整合
  ( select c.dindex , 
      p.valuecode,p.valuename,c.custcode,c.custname,c.payc,p.payp,
       count(distinct p.valuecode )over(partition by c.pk ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.pk ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk  from 
       
        ---第一层：原始数据
      ( select pk,valuecode custcode,valuename custname,bdname,payc,dindex from  w1 where  bdname='客商' ) c,
     ( select * from w1 where bdname='项目' ) p --第一层            
   where c.pk=p.pk ) --第二层
                                  )   --第三层        
     group by  valuecode,valuename,custcode,custname order by valuecode

 2013-03-25 12:46:43 更新：

 debug上面说的多项目一客商（多行多金额）导致pay双倍

应该的通过pk自连接sql写法的终结者了

--第五层：增加比例
  select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c, 
       trunc((payc/sum(payc)over(partition by gcode,valuecode))*100,2)比例c,cc 次数  from
     
     -- 第四层：按照项目客商汇总（仅 rc>=2多项目时候取借方）
   ( select  gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit2 end) payc  from 


     --第三层：对多借或多贷取付款发生额
      (select  distinct  gcode, gname, prepareddatev,no, valuecode, valuename, custcode, custname ,pk_voucher,--distinct去除借方重复
         (case when re>=2 then 0 else ccredit end)ccredit2,--多项目一客商（不同行金额不同）让客商贷方数变为0，distinct之后去除重复
        (case when re>=2 and rc>=2 then ccredit/re when re>=2 then pdebit else 0 end)
         pdebit2,re,rc from   --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方)

      --第二层：分别取数并整合
  ( select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.gcode,c.prepareddatev,c.no ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.gcode, c.prepareddatev,c.no ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk_voucher  from 
       
        ---第一层：原始数据
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,   
           gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
           gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2013'             --年份
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
           left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
            and gl_detail.debitamount<>0  
         --and gl_detail.yearv = '2012'                 --依靠pk_voucher唯一性，可以不要的条件
            and bd_bdinfo.bdname='工程项目' ) p --第一层            
   where c.pk_voucher=p.pk_voucher ) --第二层
                                  )   --第三层        
     group by gcode,gname,valuecode,valuename,custcode,custname)  --第四层
   where payc<>0
   group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc --第五层
   order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

 15:47:02 再次发现了问题：

--情况2_1：一项目（一行）一客商（多行），付款取客商各行payc 

这里取数没有问题

可是一旦一项目一客商（多行），客商金额相同的话，distinct因为没有客商行的detailindex，就完全相同，就会除去一行，造成了取数减少。

可是目前的情况，如果加上detailindex，下图的自连接四行就不能去除重复的两行。

解决方法：

在第三次select distinct和case那里增加：

(case when re>=2 then 0 else dindex end)dindex2 ，这样保证了多项目时去除重复（如上图），并且保证了单项目一客商（多行金额不同）的时候不去除

final？？？？？？？？？？？？？？？？？？？？？？！！！！！

 --第五层：增加比例
  select gcode,gname,valuecode,valuename,custcode,custname,payc,sum(payc)over(partition by gcode,valuecode) 总额c, 
       trunc((payc/sum(payc)over(partition by gcode,valuecode))*100,2)比例c,cc 次数  from
     
     -- 第四层：按照项目客商汇总（仅 rc>=2多项目时候取借方）
   ( select  gcode,gname,valuecode,valuename,custcode,custname,count(distinct pk_voucher)cc,
          sum(case when re>=2 then pdebit2 else ccredit2 end) payc  from 


     --第三层：对多借或多贷取付款发生额
      (select distinct gcode, gname, prepareddatev,no, valuecode, valuename, custcode, custname ,pk_voucher,--distinct去除借方重复
         (case when re>=2 then 0 else detailindex end)dindex2,--多项目时去除贷方重复，单项目一客商（多行金额不同）的时候不去除
         (case when re>=2 then 0 else ccredit end)ccredit2,--多项目一客商（不同行金额不同）让客商贷方数变为0，distinct之后去除重复
         (case when re>=2 and rc>=2 then ccredit/re when re>=2 then pdebit else 0 end)
         pdebit2,re,rc --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方         
          from   

      --第二层：分别取数并整合
  ( select c.detailindex ,c.gcode,c.gname,c.prepareddatev,c.no,
      p.valuecode,p.valuename,c.custcode,c.custname,c.ccredit,p.pdebit,
       count(distinct p.valuecode )over(partition by c.gcode,c.prepareddatev,c.no ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.gcode, c.prepareddatev,c.no ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk_voucher  from 
       
        ---第一层：原始数据
      ( select bd_glorgbook.glorgbookcode gcode, bd_glorgbook.glorgbookname gname,   
           gl_detail.creditamount ccredit,  gl_voucher.pk_voucher,gl_detail.prepareddatev,gl_voucher.no,
           gl_freevalue.valuecode custcode,gl_freevalue.valuename custname,gl_detail.detailindex
          from gl_detail
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
          join bd_glorgbook
            on gl_detail.pk_glorgbook =bd_glorgbook.pk_glorgbook  
               left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
         and gl_detail.periodv<>'00'   
           and gl_detail.yearv = '2012'             --年份
           and bd_glorgbook.glorgbookcode like '01%-0001'    --分公司并且排除多账簿                 
           and bd_bdinfo.bdname='客商辅助核算' ) c,
   ( select gl_voucher.pk_voucher,gl_detail.detailindex,gl_detail.debitamount pdebit, gl_freevalue.valuecode ,gl_freevalue.valuename
          from gl_detail                                            --一层：原始数据项目
          join gl_voucher
            on gl_detail.pk_voucher = gl_voucher.pk_voucher
           left join gl_freevalue
            on gl_detail.assid = gl_freevalue.freevalueid
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.periodv<>'00'   
            and gl_detail.debitamount<>0  
         --and gl_detail.yearv = '2012'                 --依靠pk_voucher唯一性，可以不要的条件
            and bd_bdinfo.bdname='工程项目' ) p --第一层            
   where c.pk_voucher=p.pk_voucher ) --第二层
                                  )   --第三层        
     group by gcode,gname,valuecode,valuename,custcode,custname)  --第四层
   where payc<>0
   group by gcode,gname,valuecode,valuename,custcode,custname,payc,cc --第五层
   order by gcode,valuecode,trunc((payc/sum(payc)over(partition by valuecode))*100,2) desc

 

 2013-03-26 09:45:49 更新

完善一下我的解决方法（pk自连接）

with w1 as(   /*总体说明：同一pk下反应此项目给其客商支付款的情况，其中payp表示以项目角度取数，payc以客商角度取数，
              同一pk下项目和客商在不同行（bdname能够说明此行是项目还是客商），要写sql将同一pk下项目和客商整理成一行，付款因情况不同取数有可能是payp或者payc等，详细见下*/
          
             --情况1：一项目（一行）一客商（一行），付款取客商payc
          select 'pk01' pk, 'p01' valuecode,'项目01' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual
             union all
           select 'pk01' pk,'c01' valuecode,'客商01' valuename,'客商' bdname,0 payp,1000 payc, 2 dindex   from dual
         
          --情况2：一项目（多行）一客商（一行），付款取客商payc           
             union all             
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,4000 payp, 0 payc,1 dindex   from dual             
             union all
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,3000 payp, 0 payc,2 dindex   from dual
             union all 
             select 'pk02' pk,'c02' valuecode,'客商02' valuename,'客商' bdname,0 payp, 7000 payc,3 dindex   from dual
             
           --情况3：一项目（一行）多客商，付款取客商各自payc 
                
            union all             
          select 'pk03' pk,'p03' valuecode,'项目03' valuename,'项目' bdname,4600 payp, 0 payc,1 dindex   from dual             
             union all
          select 'pk03' pk,'c03' valuecode,'客商03' valuename,'客商' bdname,0 payp, 3500 payc,2 dindex   from dual
             union all 
             select 'pk03' pk, 'c04' valuecode,'客商04' valuename,'客商' bdname,0 payp, 1100 payc,3 dindex   from dual
             
            --情况4：一项目（多行）多客商，付款取客商各自payc  
            
              union all             
          select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1600 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1900 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk04' pk,'c05' valuecode,'客商05' valuename,'客商' bdname,0 payp, 500 payc,3 dindex   from dual
             union all 
             select 'pk04' pk, 'c06' valuecode,'客商06' valuename,'客商' bdname,0 payp, 3000 payc,4 dindex   from dual
             
           --情况5：多项目一客商（一行），付款取项目各自payp
              
              union all             
          select 'pk05' pk,'p05' valuecode,'项目05' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk05' pk,'p06' valuecode,'项目06' valuename,'项目' bdname,2900 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk05' pk,'c07' valuecode,'客商07' valuename,'客商' bdname,0 payp, 3900 payc,3 dindex   from dual
          
          --情况6：多项目一客商（多行），付款取项目各自payp
              
              union all             
          select 'pk06' pk,'p07' valuecode,'项目07' valuename,'项目' bdname,1100 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk06' pk,'p08' valuecode,'项目08' valuename,'项目' bdname,2600 payp, 0 payc,2 dindex   from dual    
             union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 900 payc,3 dindex   from dual
          union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 2800 payc,4 dindex   from dual
          
           --情况7：多项目多客商，付款取各个客商payc/项目数量（这个情况没有法子，只好让每个项目平摊付款数，表示客商平均给项目提供材料）
              
              union all             
          select 'pk07' pk,'p09' valuecode,'项目09' valuename,'项目' bdname,50 payp, 0 payc,1 dindex   from dual             
             union all
           select 'pk07' pk,'p10' valuecode,'项目10' valuename,'项目' bdname,10500 payp, 0 payc,2 dindex   from dual    
             union all   
         select 'pk07' pk,'p11' valuecode,'项目11' valuename,'项目' bdname,50 payp, 0 payc,3 dindex   from dual   
           union all   
          select 'pk07' pk,'c09' valuecode,'客商09' valuename,'客商' bdname,0 payp, 3800 payc,5 dindex   from dual
          union all   
          select 'pk07' pk,'c10' valuecode,'客商10' valuename,'客商' bdname,0 payp, 6800 payc,4 dindex   from dual
          
                )
                
         select   valuecode,valuename,custcode,custname, 
           (case when re>=2 then payp2 else payc2 end) pay  from 

     --第三层：对多借或多贷取付款发生额
      (select distinct valuecode, valuename, custcode, custname ,pk,re,rc,--distinct去除借方重复
         (case when re>=2 then 0 else dindex end)dindex2,--多项目时去除贷方重复，单项目一客商（多行金额不同）的时候不去除
         (case when re>=2 then 0 else payc end) payc2,--除去多项目(见上层select)，一直取银行贷方
         (case when re>=2 and rc>=2 then payc/re when re>=2 then payp else 0 end)
         payp2 from   --多个借方工程项目保留借方，取数是工程施工借方；否则为0不取(为0保证单个重复项目distinct去掉重复，不影响贷方)

      --第二层：分别取数并整合
  ( select c.dindex , 
      p.valuecode,p.valuename,c.custcode,c.custname,c.payc,p.payp,
       count(distinct p.valuecode )over(partition by c.pk ) re, --re得到一个凭证中多个不同工程项目
       count(distinct c.custcode)over(partition by c.pk ) rc,--rc得到一个凭证中多个不同的银行客商
       c.pk  from 
       
        ---第一层：原始数据，通过pk自连接
      ( select pk,valuecode custcode,valuename custname,bdname,payc,dindex from  w1 where  bdname='客商' ) c,
      ( select * from w1 where bdname='项目' ) p --第一层            
   where c.pk=p.pk ) --第二层
                                  )   --第三层        
     order by valuecode
              
                

下面是newkid版主给的

“如果非得去掉自连接，我想到了一种比较奇特的方法可以把一行变多行”（结果ok，但是真心不懂，很复杂的样子）

create table  w1 as    /*总体说明：同一pk下反应此项目给其客商支付款的情况，其中payp表示以项目角度取数，payc以客商角度取数，
              同一pk下项目和客商在不同行（bdname能够说明此行是项目还是客商），要写sql将同一pk下项目和客商整理成一行，付款因情况不同取数有可能是payp或者payc等，详细见下*/
             --情况1：一项目（一行）一客商（一行），付款取客商payc
          select 'pk01' pk, 'p01' valuecode,'项目01' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual
             union all
           select 'pk01' pk,'c01' valuecode,'客商01' valuename,'客商' bdname,0 payp,1000 payc, 2 dindex   from dual
          --情况2：一项目（多行）一客商（一行），付款取客商payc           
             union all            
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,4000 payp, 0 payc,1 dindex   from dual            
             union all
          select 'pk02' pk,'p02' valuecode,'项目02' valuename,'项目' bdname,3000 payp, 0 payc,2 dindex   from dual
             union all
             select 'pk02' pk,'c02' valuecode,'客商02' valuename,'客商' bdname,0 payp, 7000 payc,3 dindex   from dual
           --情况3：一项目（一行）多客商，付款取客商各自payc
            union all            
          select 'pk03' pk,'p03' valuecode,'项目03' valuename,'项目' bdname,4600 payp, 0 payc,1 dindex   from dual            
             union all
          select 'pk03' pk,'c03' valuecode,'客商03' valuename,'客商' bdname,0 payp, 3500 payc,2 dindex   from dual
             union all
             select 'pk03' pk, 'c04' valuecode,'客商04' valuename,'客商' bdname,0 payp, 1100 payc,3 dindex   from dual
            --情况4：一项目（多行）多客商，付款取客商各自payc  
              union all            
          select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1600 payp, 0 payc,1 dindex   from dual            
             union all
           select 'pk04' pk,'p04' valuecode,'项目04' valuename,'项目' bdname,1900 payp, 0 payc,2 dindex   from dual   
             union all   
          select 'pk04' pk,'c05' valuecode,'客商05' valuename,'客商' bdname,0 payp, 500 payc,3 dindex   from dual
             union all
             select 'pk04' pk, 'c06' valuecode,'客商06' valuename,'客商' bdname,0 payp, 3000 payc,4 dindex   from dual
           --情况5：多项目一客商（一行），付款取项目各自payp
              union all            
          select 'pk05' pk,'p05' valuecode,'项目05' valuename,'项目' bdname,1000 payp, 0 payc,1 dindex   from dual            
             union all
           select 'pk05' pk,'p06' valuecode,'项目06' valuename,'项目' bdname,2900 payp, 0 payc,2 dindex   from dual   
             union all   
          select 'pk05' pk,'c07' valuecode,'客商07' valuename,'客商' bdname,0 payp, 3900 payc,3 dindex   from dual
          --情况6：多项目一客商（多行），付款取项目各自payp
              union all            
          select 'pk06' pk,'p07' valuecode,'项目07' valuename,'项目' bdname,1100 payp, 0 payc,1 dindex   from dual            
             union all
           select 'pk06' pk,'p08' valuecode,'项目08' valuename,'项目' bdname,2600 payp, 0 payc,2 dindex   from dual   
             union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 900 payc,3 dindex   from dual
          union all   
          select 'pk06' pk,'c08' valuecode,'客商08' valuename,'客商' bdname,0 payp, 2800 payc,4 dindex   from dual
           --情况7：多项目多客商，付款取各个客商payc/项目数量（这个情况没有法子，只好让每个项目平摊付款数，表示客商平均给项目提供材料）
              union all            
          select 'pk07' pk,'p09' valuecode,'项目09' valuename,'项目' bdname,50 payp, 0 payc,1 dindex   from dual            
             union all
           select 'pk07' pk,'p10' valuecode,'项目10' valuename,'项目' bdname,10500 payp, 0 payc,2 dindex   from dual   
             union all   
         select 'pk07' pk,'p11' valuecode,'项目11' valuename,'项目' bdname,50 payp, 0 payc,3 dindex   from dual   
           union all   
          select 'pk07' pk,'c09' valuecode,'客商09' valuename,'客商' bdname,0 payp, 3800 payc,5 dindex   from dual
          union all   
          select 'pk07' pk,'c10' valuecode,'客商10' valuename,'客商' bdname,0 payp, 25800 payc,4 dindex   from dual
;

---- 下面的那个TABLE()函数是关键：
SELECT pk,valuecode,valuename,custcode,custname,pay
  FROM (SELECT pk
              ,valuecode
              ,valuename
              ,MAX(CASE WHEN bdname='客商' THEN valuecode END) OVER(PARTITION BY pk, COLUMN_VALUE) custcode
              ,MAX(CASE WHEN bdname='客商' THEN valuename END) OVER(PARTITION BY pk, COLUMN_VALUE) custname
              ,CASE WHEN re>=2 and rc>=2 then MAX(CASE WHEN bdname='客商' THEN payc END) OVER(PARTITION BY pk, COLUMN_VALUE) /re
                    WHEN re>=2 THEN payp
                    ELSE MAX(CASE WHEN bdname='客商' THEN payc END) OVER(PARTITION BY pk, COLUMN_VALUE)
               END AS pay
              ,bdname
          FROM ( select pk
                       ,valuecode
                       ,valuename
                       ,bdname
                       ,SUM(payp) payp
                       ,SUM(payc) payc
                       ,MAX(dindex) dindex
                       ,COUNT(CASE WHEN bdname='项目' THEN 1 END) OVER(PARTITION BY pk) AS re
                       ,COUNT(CASE WHEN bdname='客商' THEN 1 END) OVER(PARTITION BY pk) AS rc
                       ,ROW_NUMBER() OVER(PARTITION BY pk,bdname ORDER BY MAX(dindex))  AS rn
                  from  w1
                 GROUP BY pk,valuecode,valuename,bdname
               ) p
              ,TABLE(CAST(MULTISET(SELECT CASE WHEN bdname='项目' THEN LEVEL ELSE p.rn END
                                     FROM DUAL
                                   CONNECT BY LEVEL <= CASE WHEN bdname='项目' THEN p.rc ELSE 1 END
                                   )
                          AS SYS.ODCINUMBERLIST
                          )
                     )
        )
WHERE bdname='项目';

 2013-04-03 15:56:30 小更新

每个公司2010 2011 2012三年的结算收入

写法和上面的有所不同，case的是年份

select  bd_glorgbook.glorgbookcode,
           replace(bd_glorgbook.glorgbookname,'集团基准账薄','')公司名称,        
      
           sum( case when gl_balance.year= '2010' then  gl_balance.debitamount else 0 end) "2010发生额",
           sum( case when gl_balance.year= '2011' then  gl_balance.debitamount else 0 end) "2011发生额",
           sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) "2012发生额"            
          
      from gl_balance, bd_accsubj, bd_glorgbook
    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
       and bd_glorgbook.glorgbookcode like '01%-0001' 
       and ( bd_accsubj.subjcode like '2123%')
       and gl_balance.period<>'00'
       and gl_balance.year in('2010','2011','2012')
      group by bd_glorgbook.glorgbookcode,  bd_glorgbook.glorgbookname    
    order by bd_glorgbook.glorgbookcode