【EAS 科目】后台查询分配到下级的科目辅助账类型的SQL语句（包含本级组织父科目及分配来源科目）

【EAS 科目】后台查询分配到下级的科目辅助账类型的SQL语句（包含本级组织父科目及分配来源科目）原创

叶梦琪

2人赞赏了该文章 293次浏览编辑于2022年11月15日 23:57:28

适用场景：后台查询已分配给到下级组织的某个科目的辅助账类型与当前组织中父科目、分配来源科目的辅助账类型数据

涉及到的表：

会计科目表 t_bd_accountview

科目表 t_bd_accounttable

辅助账类型表 T_BD_AsstAccount

公司表 T_ORG_Company

打开查询分析器，可参考以下查询SQL：

SELECT ac.fid as id,

ac.fnumber as 科目编码,

ac.fname_l2 as 科目名称,

ca.FNAME_l2 as 本级辅助帐类型,

ga.FNAME_l2 as 上级辅助帐类型,

ac.FPARENTID as 父科目id,

pac.FNAME_l2 as 父科目名称,

ca01.FNAME_l2 as 父科目本级辅助帐类型,

ga01.FNAME_l2 as 父科目上级辅助帐类型,

ac.FUPPERID as 分配来源科目id,

upac.fname_l2 as 分配来源科目名称,

ca02.FNAME_l2 as 分配来源科目本级辅助帐类型,

ga02.FNAME_l2 as 分配来源科目上级辅助帐类型,

ac.FCOMPANYID as 组织id,

cm.FDISPLAYNAME_l2 as 组织长名称

FROM t_bd_accountview ac

LEFT OUTER JOIN t_bd_accountview pac on ac.FPARENTID =pac.fid

left JOIN t_bd_accountview upac on ac.FUPPERID =upac.fid

INNER JOIN T_ORG_COMPANY cm ON AC.FCOMPANYID = CM.FID

left JOIN T_BD_AsstAccount ca on ac.FCAA =ca.fid

left JOIN T_BD_AsstAccount ga on ac.FGAA =ga.fid

left JOIN T_BD_AsstAccount ca01 on pac.FCAA =ca01.fid

left JOIN T_BD_AsstAccount ga01 on pac.FGAA =ga01.fid

left JOIN T_BD_AsstAccount ca02 on upac.FCAA =ca02.fid

left JOIN T_BD_AsstAccount ga02 on upac.FGAA =ga02.fid

where ac.fnumber = '科目编码'

and ac.FACCOUNTTABLEID =(

SELECT FID FROM t_bd_accounttable where FNUMBER ='科目表编码'

)

order by cm.FDISPLAYNAME_l2

赞 2