管理费用
为了得到科目名称，只好再从外面写一层
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= '' then gl_balance.debitamount else 0 end) "2014发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2013发生额",
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','')
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= '' and bd_glorgbook.glorgbookcode<>'015101-0001' then gl_balance.debitamount else 0 end) "2012发生额",
sum( case when gl_balance.year= '' and bd_glorgbook.glorgbookcode<>'015501-0001' then gl_balance.debitamount else 0 end) "2013发生额",
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','')
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= '' then gl_balance.debitamount else 0 end) "2014借方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2014贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2013借方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2013贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2012借方发生额" ,
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','')
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 = ''
and gl_detail.yearv=''
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 = ''
and gl_detail.yearv=''
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='' then gl_detail.debitamount else 0 end) "2012发生额",
sum( case when gl_detail.yearv='' then gl_detail.debitamount else 0 end) "2013发生额",
sum( case when gl_detail.yearv='' 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 = ''
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 = ''
and gl_detail.yearv = ''
and bd_glorgbook.glorgbookcode like '01%01-0001'
and gl_detail.periodv<>''
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 = ''
and gl_detail.yearv in ('','','','')
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
-- 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= '' then gl_balance.debitamount else 0 end) "2012发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2013发生额",
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','')
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('','')
and arap_djfb.dr=''
and length(bd_cubasdoc.custname)>3
and length(bd_cubasdoc.custcode)<>6
and bd_corp.unitcode not in('',''))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('','')
and arap_djfb.dr=''
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('','')
and arap_djfb.dr=''
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 = ''
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 = ''
and c.busilineno = 0 -- 0 是第一行
and c.settlestatus in('','')
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 = ''
and gl_detail.periodv<>''
and gl_detail.yearv= ''
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)