日期:2014-05-17 浏览次数:20545 次
SELECT
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel
,SUM([value]) as financeValue
,(fAcc.closeBalance + SUM(fTake.[value])) as balance
FROM (
select fAcc.id id
,fAcc.bankAccId bankAccId
,fAcc.closeDate closeDate
,fAcc.closeBalance closeBalance
,fAcc.isActive isActive
,fAcc.viewLevel viewLevel
,fTake.[value] [value]
from dbo.finance_account AS fAcc
INNER JOIN dbo.finance_taking AS fTake
ON fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate
) t
GROUP BY
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel
declare @val varchar(max)
declare @sql varchar(max)
set @val='facc.id,facc.bankaccid,facc.closedate,
facc.closebalance,facc.isactive'
set @sql='select '+@sql+',sum(ftake.[value]) as financevalue,(
facc.closebalance+sum(ftake.[value])) as balance from dbo.finance_account as
facc inner join dbo.finance_taking as ftake on ftake.financeaccid=
facc.id and ftake.[date]>facc.closedate group by '+@sql
exec (@sql)