建工项目对账查询引擎sql

最新推荐文章于 2024-12-06 12:14:35 发布

转载最新推荐文章于 2024-12-06 12:14:35 发布 · 106 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sumsen/archive/2012/06/06/2538315.html

本文介绍了一个复杂的SQL查询案例，该查询用于从多个表中选择特定条件的数据，并通过联合操作将两个相似但略有不同的数据集合并在一起。查询涉及多表连接、子查询、左连接等高级SQL概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select * from ((select gl_detail.explanation,
       bd_accsubj.dispname,
       gl_detail.debitamount 借方,
       gl_detail.creditamount 贷方,
       gl_detail.yearv,
       gl_detail.periodv ,
       gl_voucher.no 凭证号,       
       wmsys.wm_concat(gl_freevalue.valuecode) valuecode,
       wmsys.wm_concat(gl_freevalue.valuename) valuename
  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.yearv>=#byear#
    and gl_detail.pk_systemv!='TR'
and bd_accsubj.subjcode not in('41040107','41040109','41040207','41040209','41040307','41040308')
   and bd_glorgbook.glorgbookcode = (select glorgbookcode from bd_glorgbook where  pk_glbook='0001AA10000000004VTQ' and 
   (substr(glorgbookcode,1,6)=(select unitcode from bd_corp where pk_corp=#LoginCorp#)) )
   and gl_freevalue.valuecode like #duty# ||'%' 
group by gl_detail.explanation,
          bd_accsubj.dispname,
          gl_detail.creditamount,
          gl_detail.debitamount,
          gl_detail.yearv,
          gl_detail.periodv,
          gl_voucher.no)
 union 
 (select gl_detail.explanation,
       bd_accsubj.dispname,
       gl_detail.debitamount 借方,
       gl_detail.creditamount 贷方,
       gl_detail.yearv,
       gl_detail.periodv ,
       gl_voucher.no 凭证号,       
       wmsys.wm_concat(gl_freevalue.valuecode) valuecode,
       wmsys.wm_concat(gl_freevalue.valuename) valuename
  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.yearv>=#byear#
and gl_detail.pk_systemv!='TR'
   and bd_glorgbook.glorgbookcode = (select glorgbookcode from bd_glorgbook where  pk_glbook='0001AA10000000004VTQ' and 
   (substr(glorgbookcode,1,6)=(select unitcode from bd_corp where pk_corp=#LoginCorp#)) )
   and 
( bd_accsubj.subjcode  like '2120'||substr(#accsubj#,-2,2)||'%' 
or
( bd_accsubj.subjcode='5402' and gl_detail.explanation like '%' ||#explain#||'%')
)
 group by gl_detail.explanation,
          bd_accsubj.dispname,
          gl_detail.creditamount,
          gl_detail.debitamount,
          gl_detail.yearv,
          gl_detail.periodv,
          gl_voucher.no))
 order by yearv,periodv,dispname

转载于:https://www.cnblogs.com/sumsen/archive/2012/06/06/2538315.html