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
