select 合同名称,合同额,回款额, 欠款额=合同额-(select sum(回款额) from 表 where 表.合同名称=a.合同名称) from 表 a
那你的回款数据里的有时间吧,要不怎么区分那个先回的啊!如果有时间的话!可以 select 合同名称,合同额,回款额,欠款额=合同额-(select sum(回款额) from 表 where 表.合同名称=a.合同名称 and 表.回款时间<=a.回款时间) from 表 a我觉得如果表结构能该的话!你在回款的表中直接就插入回完款的余额多好啊!那样查询也方便,快速
select identity(int,1,1) as id,b.* into #test from tablename bselect a.合同名称,a.合同额,a.回款额,欠款额=a.合同额-(select sum(回款额) from #test where #test.合同名称=a.合同名称 and #test.id<=a.id) from 表 #test a
select a.iID, a.iContractID, b.cContractName, b.nMoney as 合同额, a.nMoney as 回款额, dReturn, 欠款额 = b.nMoney - (select sum(nMoney) from tMkReturnMoney s where s.iContractID=a.iContractID and s.dReturn <= a.dReturn --and s.iID <= a.iID ) from tMkReturnMoney a left outer join tMkContract b on a.iContractID = b.iContractID order by dReturn
欠款额=合同额-(select sum(回款额) from 表 where 表.合同名称=a.合同名称)
from 表 a
select 合同名称,合同额,回款额,欠款额=合同额-(select sum(回款额) from 表 where 表.合同名称=a.合同名称 and 表.回款时间<=a.回款时间) from 表 a我觉得如果表结构能该的话!你在回款的表中直接就插入回完款的余额多好啊!那样查询也方便,快速
欠款额 = b.nMoney - (select sum(nMoney) from tMkReturnMoney s
where s.iContractID=a.iContractID and s.dReturn <= a.dReturn --and s.iID <= a.iID
)
from tMkReturnMoney a left outer join tMkContract b on a.iContractID = b.iContractID
order by dReturn