建工发债sql

管理费用

为了得到科目名称，只好再从外面写一层

select a.*,
(select b.subjname from bd_accsubj b where b.subjcode=a.scode  and b.pk_glorgbook='0001E1100000000000MX') 项目--从总部机关的会计科目得到名称
 
 from (
   select substr(bd_accsubj.subjcode,1,6)scode,     
             sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) "2014发生额", 
             sum( case when gl_balance.year= '2013' then  gl_balance.debitamount else 0 end) "2013发生额",  
             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 '5502%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2012','2013','2014')
      group by  substr(bd_accsubj.subjcode,1,6)
    order by  substr(bd_accsubj.subjcode,1,6))a 
    order by  a.scode
 

 去除2012总一，2013总五 case 增加条件，完美

select a.*,b.subjname from (
   select       substr(bd_accsubj.subjcode,1,6)scode,
           sum( case when gl_balance.year= '2012' and bd_glorgbook.glorgbookcode<>'015101-0001'   then  gl_balance.debitamount else 0 end) "2012发生额",
           sum( case when gl_balance.year= '2013' and bd_glorgbook.glorgbookcode<>'015501-0001'  then  gl_balance.debitamount else 0 end) "2013发生额",
           sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) "2014发生额"

      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 '5502%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2012','2013','2014')
      group by  substr(bd_accsubj.subjcode,1,6)

    order by  substr(bd_accsubj.subjcode,1,6))a,bd_accsubj b
    where a.scode=b.subjcode
    and b.pk_glorgbook='0001E1100000000000MX'
    order by a.scode

 

 

银行存款的发生额

 
   select  bd_accsubj.subjcode,bd_accsubj.subjname,     
             sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) "2014借方发生额", 
              sum( case when gl_balance.year= '2014' then  gl_balance.creditamount else 0 end) "2014贷方发生额", 
             sum( case when gl_balance.year= '2013' then  gl_balance.debitamount else 0 end) "2013借方发生额",  
             sum( case when gl_balance.year= '2013' then  gl_balance.creditamount else 0 end) "2013贷方发生额",  
             sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) "2012借方发生额" ,
             sum( case when gl_balance.year= '2012' then  gl_balance.creditamount 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 = '030101-0001'
       and (bd_accsubj.subjcode like '1002%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2012','2013','2014')
      group by bd_accsubj.subjcode,bd_accsubj.subjname 
    order by  bd_accsubj.subjcode 

 去除年末调账的项目成本和结算（NC科目余额和投标报表数字不一致）

select glorgbookcode,  nvl(replace(glorgbookname,'集团基准账薄',''),'小计')公司名称,
valuecode,valuename,sum(借方)成本,sum(贷方)收入 from

( select gl_freevalue.valuecode,
      gl_freevalue.valuename,
gl_detail.prepareddatev 制单日期 , 
       gl_voucher.no 凭证号, 
 gl_detail.explanation,
      bd_accsubj.dispname,       
       gl_detail.debitamount 借方,   
        gl_detail.creditamount 贷方,
        gl_detail.pk_systemv,
        gl_voucher.pk_voucher,
        bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname           
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and gl_detail.yearv='2013'
   and bd_glorgbook.glorgbookcode = '010201-0001'
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%' or bd_accsubj.subjcode like '4104%')--只要工程施工和工程结算
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
   and gl_detail.pk_voucher not in('1038N51000000000RIGV','1038N51000000000RIKG','1038N51000000000RIM3')
   -- 去除北京分包项目结算（'1038N51000000000RIGV'）、税金（'1038N51000000000RIKG'）、成本（'1038N51000000000RIM3'）
   order by gl_detail.yearv,gl_detail.periodv,bd_accsubj.dispname,gl_voucher.no )
  
  group by glorgbookcode,rollup((glorgbookname, valuecode,valuename))
  order by glorgbookcode,valuecode

 2015-11-13 09:38:00

审计要的项目成本二级明细

select glorgbookcode, glorgbookname,valuecode,valuename,
substr(subjcode,1,8) 科目编码,subjname 科目名称,sum(借方)成本 from

(    select gl_freevalue.valuecode,
      gl_freevalue.valuename,
gl_detail.prepareddatev 制单日期 , 
       gl_voucher.no 凭证号, 
 gl_detail.explanation,
      bd_accsubj.subjcode,   
      bd_accsubj.subjname,    
       gl_detail.debitamount 借方,   
        gl_detail.creditamount 贷方,
        gl_detail.pk_systemv,
        gl_voucher.pk_voucher,
        bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname           
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and gl_detail.yearv='2013'
   and bd_glorgbook.glorgbookcode like '01%-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '410401%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
  
   order by gl_detail.yearv,gl_detail.periodv,gl_voucher.no   )
  
  group by glorgbookcode,glorgbookname, valuecode,valuename,substr(subjcode,1,8),subjname
  order by glorgbookcode,valuecode,substr(subjcode,1,8)

 三年合一

  select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
   gl_freevalue.valuecode,
     gl_freevalue.valuename, 
      substr(subjcode, 1,8),  
      bd_accsubj.subjname,          
            sum( case when gl_detail.yearv='2012' then  gl_detail.debitamount else 0 end) "2012发生额",
           sum( case when gl_detail.yearv='2013' then   gl_detail.debitamount else 0 end) "2013发生额",
           sum( case when gl_detail.yearv='2014' then   gl_detail.debitamount else 0 end) "2014发生额"                  
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'   
   and bd_glorgbook.glorgbookcode like '01%-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '410401%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
  
  group by glorgbookcode,glorgbookname, valuecode,valuename, substr(bd_accsubj.subjcode, 1,8)  ,subjname
  order by glorgbookcode,valuecode,substr(subjcode,1,8)

 2016-11-02 10:22:30

施书云要管理费用各公司

select bd_glorgbook.glorgbookname,
gl_detail.prepareddatev 制单日期,
gl_detail.explanation,
       bd_accsubj.dispname,
       gl_detail.debitamount 借方,
       gl_detail.creditamount 贷方,       
       gl_voucher.no 凭证号
  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
 
 where gl_detail.dr = '0'
   and gl_detail.yearv = '2015'
   and bd_glorgbook.glorgbookcode like '01%01-0001'  
   and gl_detail.periodv<>'00'
   and  gl_detail.debitamount<>0
  /* and gl_detail.explanation='付财大凤凰楼项目人工费(洪荣森)'*/
 and ( bd_accsubj.subjcode  like '550216%' or bd_accsubj.subjcode like'550207%' )
 
 order by bd_glorgbook.glorgbookcode,gl_detail.prepareddatev, bd_accsubj.subjcode

 

 

税务局审计要的营业外支出

  select     bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
gl_detail.prepareddatev 制单日期 , 
       gl_voucher.no 凭证号, 
 gl_detail.explanation 摘要,
   bd_accsubj.dispname 科目,       
       gl_detail.debitamount 发生额        
          
      
         from gl_detail, bd_accsubj, bd_glorgbook, gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
 
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and gl_detail.yearv in ('2011','2012','2013','2014')
   and bd_glorgbook.glorgbookcode like '01%01-0001'  
 
   and  bd_accsubj.subjcode like '5601%'  
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
 
   order by gl_detail.yearv,bd_glorgbook.glorgbookcode,gl_detail.periodv,gl_voucher.no 
 

 改进的工程收款sql：至本年末收款等（强大大大大大大）

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
   gl_freevalue.valuecode,
     gl_freevalue.valuename, 
           sum( case when gl_detail.yearv<=2014 then   gl_detail.creditamount else 0 end) "至本年末收款",  
            sum( case when gl_detail.yearv<2014 then  gl_detail.creditamount else 0 end) "至上年末收款",
           sum( case when gl_detail.yearv=2014 then   gl_detail.creditamount else 0 end) "本年收款"                  
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'   
   and bd_glorgbook.glorgbookcode like '01%-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
     group by glorgbookcode,glorgbookname, valuecode,valuename
  order by glorgbookcode,valuecode

 2016-08-24 11:37:55

责任人级别

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
  substr(gl_freevalue.valuecode,1,6)"jobcode" ,
     (select jobname from bd_jobbasfil where jobcode=substr(gl_freevalue.valuecode,1,6)) "jobname" , 
           sum( case when gl_detail.yearv=2013 then   gl_detail.creditamount else 0 end)/10000 "2013",  
            sum( case when gl_detail.yearv=2014 then  gl_detail.creditamount else 0 end)/10000 "2014",
           sum( case when gl_detail.yearv=2015 then   gl_detail.creditamount else 0 end)/10000 "2015"                  
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'   
   and bd_glorgbook.glorgbookcode like '01%-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
     group by glorgbookcode,glorgbookname,   substr(gl_freevalue.valuecode,1,6)
  order by glorgbookcode,  substr(gl_freevalue.valuecode,1,6)

 2016-09-13 09:57:52

单独一个公司NC开始之后的收入

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,
    gl_freevalue.valuecode ,
    gl_freevalue.valuename,
     (select begindate from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "开工时间" ,
      (select forecastenddate from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "预计完工时间" ,
           sum(gl_detail.creditamount )/10000 "收入"


         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and bd_glorgbook.glorgbookcode like '0104%-0001'
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
     group by glorgbookcode,glorgbookname,   gl_freevalue.valuecode, gl_freevalue.valuename
  order by glorgbookcode

 2016-09-27 16:06:47 山东 秦经理要的

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,
 gl_freevalue.valuecode "jobcode" ,
    gl_freevalue.valuename "jobname" ,
    (select jobname from bd_jobbasfil where jobcode=substr(gl_freevalue.valuecode,1,6)) "责任人" , 
    (select def2 from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "合同额" , 

              sum( case when gl_detail.yearv=2010 then   gl_detail.creditamount else 0 end)/10000 "2010",
            sum( case when gl_detail.yearv=2011 then  gl_detail.creditamount else 0 end)/10000 "2011",
           sum( case when gl_detail.yearv=2012 then   gl_detail.creditamount else 0 end)/10000 "2012" ,

           sum( case when gl_detail.yearv=2013 then   gl_detail.creditamount else 0 end)/10000 "2013",
            sum( case when gl_detail.yearv=2014 then  gl_detail.creditamount else 0 end)/10000 "2014",
           sum( case when gl_detail.yearv=2015 then   gl_detail.creditamount else 0 end)/10000 "2015",
           sum( case when gl_detail.yearv=2016 then   gl_detail.creditamount else 0 end)/10000 "2016"

         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and bd_glorgbook.glorgbookcode like '0104%-0001'
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
     group by glorgbookcode,glorgbookname,    gl_freevalue.valuecode, gl_freevalue.valuename
  order by bd_glorgbook.glorgbookcode, gl_freevalue.valuecode

 加上2016.6月的

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,
 gl_freevalue.valuecode "jobcode" ,
    gl_freevalue.valuename "jobname" ,
    (select jobname from bd_jobbasfil where jobcode=substr(gl_freevalue.valuecode,1,6)) "责任人" , 
    (select def2 from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "合同额" , 
    (select begindate from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "开工时间" ,
      (select forecastenddate from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "预计完工时间" ,


              sum( case when gl_detail.yearv=2010 and bd_glorgbook.glorgbookcode= '010402-0001' then   gl_detail.creditamount else 0 end)/10000 "2010",
            sum( case when gl_detail.yearv=2011  and bd_glorgbook.glorgbookcode= '010401-0001'then  gl_detail.creditamount else 0 end)/10000 "2011",
           sum( case when gl_detail.yearv=2012 then   gl_detail.creditamount else 0 end)/10000 "2012" ,

           sum( case when gl_detail.yearv=2013 then   gl_detail.creditamount else 0 end)/10000 "2013",
            sum( case when gl_detail.yearv=2014 then  gl_detail.creditamount else 0 end)/10000 "2014",
           sum( case when gl_detail.yearv=2015 then   gl_detail.creditamount else 0 end)/10000 "2015",
           sum( case when gl_detail.yearv=2016 and gl_detail.periodv<=6 then   gl_detail.creditamount else 0 end)/10000 "2016"

         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and bd_glorgbook.glorgbookcode like '0104%-0001'
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
     group by glorgbookcode,glorgbookname,    gl_freevalue.valuecode, gl_freevalue.valuename
  order by bd_glorgbook.glorgbookcode, gl_freevalue.valuecode

 

收入成本合成

select * from (
select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
   gl_freevalue.valuecode,
     gl_freevalue.valuename, 
        
           sum( case when gl_detail.yearv=2012 then   gl_detail.creditamount else 0 end) INCOME,    
           sum( case when gl_detail.yearv=2012 then   gl_detail.debitamount else 0 end) COST                
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'   
   and bd_glorgbook.glorgbookcode like '03%-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%' or bd_accsubj.subjcode like '4104%' )--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
  
     group by glorgbookcode,glorgbookname, valuecode,valuename)
     where INCOME<>0 OR COST<>0
  order by glorgbookcode,valuecode

加上发包方

select a.*,b.def1 from 
(select * from (
select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,   
   gl_freevalue.valuecode,
     gl_freevalue.valuename, 
        
           sum( case when gl_detail.yearv=2015 then   gl_detail.creditamount else 0 end) INCOME,    
           sum( case when gl_detail.yearv=2015 then   gl_detail.debitamount else 0 end) COST                
      
         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'   
   and bd_glorgbook.glorgbookcode like '03%01-0001'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%' or bd_accsubj.subjcode like '4104%' )--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
  
     group by glorgbookcode,glorgbookname, valuecode,valuename)
     where INCOME<>0 OR COST<>0) a,bd_jobbasfil b
     where a.valuecode=b.jobcode
  order by glorgbookcode,valuecode

 发包方改良

select bd_glorgbook.glorgbookcode,
        bd_glorgbook.glorgbookname,
 gl_freevalue.valuecode "jobcode" ,
    gl_freevalue.valuename "jobname" ,
    (select jobname from bd_jobbasfil where jobcode=substr(gl_freevalue.valuecode,1,6)) "责任人" , 
      (select def1 from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "发包方" ,
    (select def2 from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "合同额" ,
   

              sum(  gl_detail.creditamount) "收款"
            

         from gl_detail, bd_accsubj, bd_glorgbook,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
   and bd_glorgbook.glorgbookcode = '011601-0001'
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转
     group by glorgbookcode,glorgbookname,    gl_freevalue.valuecode, gl_freevalue.valuename
  order by bd_glorgbook.glorgbookcode, gl_freevalue.valuecode

 2017-06-20 09:57:23 最终版

select   gl_freevalue.valuecode "项目编码",
  nvl(gl_freevalue.valuename,'小计') "项目名称",  
 (select jobname from bd_jobbasfil where jobcode=substr(gl_freevalue.valuecode,1,6)) "责任人" ,
      (select def1 from bd_jobbasfil where jobcode=gl_freevalue.valuecode)"发包方" ,
    (select def2 from bd_jobbasfil where jobcode=gl_freevalue.valuecode) "合同额" ,
    
        sum( case when gl_detail.yearv=2008 then   gl_detail.creditamount else 0 end)/10000 "2008",  
            sum( case when gl_detail.yearv=2009 then  gl_detail.creditamount else 0 end)/10000 "2009",
           sum( case when gl_detail.yearv=2010 then   gl_detail.creditamount else 0 end)/10000 "2010",
    
        sum( case when gl_detail.yearv=2011 then   gl_detail.creditamount else 0 end)/10000 "2011",  
            sum( case when gl_detail.yearv=2012 then  gl_detail.creditamount else 0 end)/10000 "2012",
           sum( case when gl_detail.yearv=2013 then   gl_detail.creditamount else 0 end)/10000 "2013",
               sum( case when gl_detail.yearv=2014 then   gl_detail.creditamount else 0 end)/10000 "2014",  
            sum( case when gl_detail.yearv=2015 then  gl_detail.creditamount else 0 end)/10000 "2015",
           sum( case when gl_detail.yearv=2016 then   gl_detail.creditamount else 0 end)/10000 "2016",
           
           
            sum( case when gl_detail.yearv<2017 then  gl_detail.creditamount else 0 end)/10000 "至上年末收款",
           sum( case when gl_detail.yearv=2017 and gl_detail.periodv<=5  then   gl_detail.creditamount else 0 end)/10000 "本年收款",  
              sum( case when gl_detail.prepareddatev<='2017-05-31'   then   gl_detail.creditamount else 0 end)/10000 "合计" 

               
           
         from gl_detail, bd_accsubj, bd_corp,gl_freevalue,gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_corp =  bd_corp.pk_corp
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_detail.dr = '0'
  -- and gl_detail.prepareddatev between #date1# and #date2#
   and bd_corp.unitcode like '0104%'  
 -- and  gl_freevalue.valuecode like #job1#||'%'
   and length(gl_freevalue.valuecode)=10
   and  bd_accsubj.subjcode like '2123%' 
   and gl_voucher.discardflag='N'
   and gl_detail.pk_systemv not in('gl','TR')  
   and gl_detail.pk_glbook='0001AA10000000004VTQ'
     group by substr(gl_freevalue.valuecode,1,6),   rollup((  gl_freevalue.valuecode , gl_freevalue.valuename ))
 order by   substr(gl_freevalue.valuecode,1,6)

 

各种税（使用余额表）

   select     bd_accsubj.subjcode,bd_accsubj.dispname,
           sum( case when gl_balance.year= '2012'     then  gl_balance.debitamount else 0 end) "2012发生额",
           sum( case when gl_balance.year= '2013'    then  gl_balance.debitamount else 0 end) "2013发生额",
           sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) "2014发生额"

      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 '2171%' or bd_accsubj.subjcode like '2176%' )
       and gl_balance.period<>'00'
       and gl_balance.year in('2012','2013','2014')
      group by   bd_accsubj.subjcode, bd_accsubj.dispname

    order by  bd_accsubj.subjcode

 完成每个公司前100付款客商

select * from (
select TA.*,rank()over(partition by 付款单位 order by 付款总金额 desc) rn from
(
select distinct bd_corp.unitcode, bd_corp.unitname as 付款单位,
bd_cubasdoc.custcode as 收款单位编码 ,bd_cubasdoc.custname as 收款单位名称, 
sum(arap_djfb.bbye) over(partition by bd_corp.unitcode,bd_cubasdoc.custcode )as 付款总金额 

from bd_cubasdoc,arap_djfb,bd_cumandoc,arap_djzb,bd_corp
where arap_djfb.ksbm_cl=bd_cumandoc.pk_cumandoc 
and bd_cumandoc.pk_cubasdoc= bd_cubasdoc.pk_cubasdoc 
and arap_djfb.vouchid= arap_djzb.vouchid
and bd_corp.pk_corp=arap_djzb.dwbm
and arap_djfb.payflag in('1','2')
and arap_djfb.dr='0' 
and length(bd_cubasdoc.custname)>3
and length(bd_cubasdoc.custcode)<>6
and bd_corp.unitcode not in('0100','010101'))TA)
where rn<=100

直接order by 的话出现的结果只有钢结构，因此需要partition 才能出现各个公司的前100

使用partition 必须order by，并且从大到小排练还要desc（默认asc从小到大）

 供应商top

select  
bd_cubasdoc.custcode as 收款单位编码 ,bd_cubasdoc.custname as 收款单位名称, 
sum(arap_djfb.bbye) 收款总金额

from bd_cubasdoc,bd_cumandoc,arap_djfb,arap_djzb 
where arap_djfb.ksbm_cl=bd_cumandoc.pk_cumandoc 
and bd_cumandoc.pk_cubasdoc= bd_cubasdoc.pk_cubasdoc 
and arap_djfb.vouchid= arap_djzb.vouchid
 
and arap_djfb.payflag in('1','2')
and arap_djfb.dr='0' 
and length(bd_cubasdoc.custname)>3
and length(bd_cubasdoc.custcode) not in (6,4)
and arap_djzb.djkjnd=2014
group by bd_cubasdoc.custcode,bd_cubasdoc.custname ,arap_djfb.bbye
order by sum(arap_djfb.bbye) desc

 

增加日期：and arap_djzb.djrq between  '2015-01-01'and '2015-09-31'

6.27 改进

select  
 bd_cubasdoc.custname as 收款单位名称, 
sum(arap_djfb.bbye) 收款总金额

from bd_cubasdoc,bd_cumandoc,arap_djfb,arap_djzb 
where arap_djfb.ksbm_cl=bd_cumandoc.pk_cumandoc 
and bd_cumandoc.pk_cubasdoc= bd_cubasdoc.pk_cubasdoc 
and arap_djfb.vouchid= arap_djzb.vouchid
 
and arap_djfb.payflag in('1','2')
and arap_djfb.dr='0' 
and length(bd_cubasdoc.custname)>3
and length(bd_cubasdoc.custcode) not in (6,4)
 
and arap_djzb.djrq between  '2016-01-01'and '2016-03-31'
group by  bd_cubasdoc.custname  
order by sum(arap_djfb.bbye) desc

 2016-12-14 16:39:47 

因为8.6号升级NC5.7，djzb失效，只能取委托付款的

select m.company_r,sum(m.totalamount) from fts_newpayment m
where m.billdate   between  '2016-01-01'and '2016-09-30'
       and m.netpaystatus=0
    group by m.company_r
order by sum(m.totalamount) desc

 

甲方top

select  v.def1,
   gl_freevalue.valuecode,
     gl_freevalue.valuename, 
        
           sum( case when gl_detail.prepareddatev between  '2016-01-01'and '2016-03-31' then   gl_detail.creditamount else 0 end) "本年收款"                  
      
         from gl_detail, bd_accsubj,  gl_freevalue,gl_voucher,bd_jobbasfil v
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
  
   and   gl_detail.assid=gl_freevalue.freevalueid
   and gl_detail.pk_voucher=gl_voucher.pk_voucher
   and gl_freevalue.valuecode=v.jobcode
   and gl_detail.dr = '0'   
   and v.jobcode like '01%'  
   and length(gl_freevalue.valuecode)=10
   and (bd_accsubj.subjcode like '2123%')--工程施工总包二级明细
   and gl_voucher.discardflag='N'--不能是作废的凭证
   and gl_detail.pk_systemv not in('gl','TR') --去除gl(小写 期初)，大写手工录入，TR自定义结转 
     group by v.def1, valuecode,valuename
  order by sum( case when gl_detail.prepareddatev between  '2016-01-01'and '2016-03-31' then   gl_detail.creditamount else 0 end) desc

甲方需要bd_jobmnfil的def1

真正做的时候select和group去掉 valuecode,valuename，因为有可能一个甲方有好几个和建工合作的项目

2017-05-05 15:36:01 客商top

select d.custname,sum(c2.local) from

 cmp_detail c, bd_cubasdoc d, cmp_settlement c2
  where c.pk_cubasdoc = d.pk_cubasdoc       
   and c.pk_settlement = c2.pk_settlement
   --and c.pk_account = bd.pk_bankaccbas
   --and c.pk_corp = '1038'
   and c.billdate between  '2016-01-01' and '2016-12-31'
   and c.dr = '0'
   and c.busilineno = 0 -- 0 是第一行
   and c.settlestatus in('5','1')
   group by d.custname
   order by 2 desc

 

2016-10-17 16:19:26

NC 升级57后单据变位arap_djfb_old,arap_djzb_old

劳务收入明细 2016.3.7

select  bd_glorgbook.glorgbookcode, bd_glorgbook.glorgbookname,gl_detail.prepareddatev 制单日期,
gl_detail.explanation,
     gl_voucher.no           凭证号,
       bd_accsubj.dispname,
       
       gl_detail.creditamount  工程款 
       
 
  from gl_detail, bd_accsubj, bd_glorgbook, gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook      
   and gl_detail.pk_voucher = gl_voucher.pk_voucher
   and gl_detail.dr = '0'
  and gl_detail.periodv<>'00'
   and gl_detail.yearv= '2015'
   and bd_glorgbook.glorgbookcode like '03%01-0001'  
   and bd_accsubj.subjcode like '2123%' 
   and gl_detail.creditamount<>0
  -- and (gl_voucher.no='263' or gl_voucher.no='207')
   --and bd_accsubj.subjcode like '150103%'
 order by bd_glorgbook.glorgbookcode,gl_detail.prepareddatev

 

转

Oracle中rank() over, dense_rank(), row_number() 的区别

假设现在有一张学生表student，学生表中有姓名、分数、课程编号，现在我需要按照课程对学生的成绩进行排序。

select * from student

1. rank over ()可以实现对学生排名，特点是成绩相同的两名是并列，如下1 2 2 4 5

select name,
      course,
      rank() over(partition by course order by score desc) as rank
  from student;

2. dense_rank()和rank over()很像，但学生成绩并列后并不会空出并列所占的名次，如下1 2 2 3 4

select name,
      course,
      dense_rank() over(partition by course order by score desc) as rank
  from student;

3. row_number这个函数不需要考虑是否并列，那怕根据条件查询出来的数值相同也会进行连续排名

select name,
      course,
      row_number() over(partition by course order by score desc) as rank
  from student;

答疑：

1. partition by用于给结果集进行分区。

2. partition by和group by有何区别？

partition by只是将原始数据进行名次排列(记录数不变)

group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)

3. 使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。

可以这样： rank over(partition by course order by score desc nulls last)