北京周聪项目客商支付join用法

最新推荐文章于 2024-10-07 20:12:49 发布

weixin_33966095

最新推荐文章于 2024-10-07 20:12:49 发布

阅读量101

点赞数

根据制单表，此博客旨在统计每个项目的客商支付情况，通过SQL查询实现，涉及详细的数据筛选和分组聚合。

需求：根据每个制单表上客商和项目辅助（不在同一行），统计出每个项目发生的客商支付情况。

sql：感谢南京赵泉

select valuecode, 项目, 客商, sum(贷方), count(*)
  from (select distinct *
          from (select b.valuecode,
                       b.valuename   项目,
                       a.valuename   客商,
                       a.贷方,
                       a.制单日期,
                       a.凭证号,
                       a.detailindex
                  from (select gl_detail.explanation,  
                               bd_accsubj.dispname,
                               gl_detail.debitamount   借方,
                               gl_detail.creditamount  贷方,
                               gl_detail.prepareddatev 制单日期,
                               gl_voucher.no           凭证号,
                               gl_freevalue.valuecode,
                               gl_freevalue.valuename,
                               gl_detail.detailindex,
                               gl_detail.pk_systemv
                          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
                         where gl_detail.dr = '0'
                           and gl_detail.explanation <> '期初'
                           and gl_detail.yearv = '2011'
                           and gl_detail.periodv = '07'
                           and bd_glorgbook.glorgbookcode = '010201-0001'
                           and length(gl_freevalue.valuecode) = '11') a   ---客商
                  join
                
                 (select gl_detail.explanation,
                        bd_accsubj.dispname,
                        gl_detail.debitamount   借方,
                        gl_detail.creditamount  贷方,
                        gl_detail.prepareddatev 制单日期,
                        gl_voucher.no           凭证号,
                        gl_freevalue.valuecode,
                        gl_freevalue.valuename,
                        gl_detail.detailindex,
                        gl_detail.pk_systemv
                   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
                  where gl_detail.dr = '0'
                    and gl_detail.explanation <> '期初'
                    and gl_detail.yearv = '2011'
                    and gl_detail.periodv = '07'
                    and bd_glorgbook.glorgbookcode = '010201-0001'
                    and length(gl_freevalue.valuecode) = '10') b  ---项目
                    on a.制单日期 = b.制单日期
                   and a.凭证号 = b.凭证号))
 group by valuecode, 项目, 客商
 order by valuecode