金蝶wise获取所有客户的科目余额

select
k3.FNumber 客户代码,
SUM(k.FEndBalance) 余额
 from(select r.FAccountID,r.FDetailID,SUM(FEndBalance) as FEndBalance
from (select u1.FAccountID,u1.FDetailID,u1.FEndBalance*t1.FDC as FEndBalance
from t_Balance  u1 inner join t_Account t1 on u1.FAccountID=t1.FAccountID
where u1.FAccountID in 
(select a.FAccountID from  t_Account a inner join t_ItemDetail b on a.FDetailID=b.FDetailID and b.F1<>0)
and  FYear=(select FValue from t_SystemProfile where Fkey='CurrentYear' and FCategory='GL') and FPeriod=(select FValue from t_SystemProfile where Fkey='CurrentPeriod' and FCategory='GL') and u1.FCurrencyID=0 and u1.FDetailID<>0
union all
select t1.FAccountID,t1.FDetailID,(CASE WHEN t1.FDC=1 THEN 1 ELSE -1 END)*FAmount*t2.FDC  as FEndBalance from  t_Voucher u1
inner join t_VoucherEntry t1 on u1.FVoucherID=t1.FVoucherID
inner join t_Account t2 on t2.FAccountID=t1.FAccountID 
where t1.FAccountID in (select a.FAccountID from  t_Account a inner join t_ItemDetail b on a.FDetailID=b.FDetailID and b.F1<>0)
and u1.FPosted=0) r group by r.FAccountID,r.FDetailID) k
inner join t_ItemDetail k1 on k1.FDetailID=k.FDetailID
inner join t_Account k2  on k2.FAccountID=k.FAccountID AND  k2.FNumber LIKE '1122.%'
inner join t_Item k3 on k3.FItemID=k1.F1
WHERE k.FEndBalance<>0
group by k3.FNumber

非原创 原作者论坛hzfw2008