不写两次可以这么写
select
AccountId,
Spend,
ClickFee,
ClickFee - Spend as RemnantMoney
(select
@AccountId as AccountId,
Spend,
(select sum(ClickFee) from b where AdCampaignId=Id) as ClickFee
FROM
a
where
AccountId=@AccountId)
select
AccountId,
Spend,
ClickFee,
ClickFee - Spend as RemnantMoney
(select
@AccountId as AccountId,
Spend,
(select sum(ClickFee) from b where AdCampaignId=Id) as ClickFee
FROM
a
where
AccountId=@AccountId)
declare @a table(单号 varchar(100) ,产品名 varchar(100), 出货量 int, 总金额 int ,总已付款 int)
declare @b table (单号 varchar(100), 本次付款 int, 付款日期 datetime)insert into @a select '001', '大豆', 100, 500, 200
insert into @a select '001', '大米', 50, 500, 200
insert into @a select '001', '红豆', 20, 500, 200
insert into @a select '002', '粟米', 100, 600, 400
insert into @a select '002', '小米', 100, 600, 400
insert into @a select '002', '黑豆', 100, 600, 400insert into @b select '001', 50, '2006-10-5'
insert into @b select '001', 100 , '2006-10-11'
insert into @b select '001', 50, '2006-10-31'
insert into @b select '002', 200, '2006-10-29'
insert into @b select '002', 200, '2006-11-1'
--insert into @b select '002', 30, '2006-11-1 10:00:0'--解决方法
select distinct e.单号,f.总金额,今天总已付款=e.总已付款,总欠付款=f.总金额-e.总已付款,e.付款日期 from @a f inner join
(
select 单号,付款日期,总已付款=(select sum(总已付款) from (select 单号,总已付款=sum(本次付款),付款日期=convert(varchar(10),付款日期,120) from @b
group by 单号,convert(varchar(10),付款日期,120)) b where a.单号=b.单号 and b.付款日期<=a.付款日期)
from
(
select 单号,总已付款=sum(本次付款),付款日期=convert(varchar(10),付款日期,120) from @b
group by 单号,convert(varchar(10),付款日期,120)
)a
)e
on e.单号=f.单号
(
select distinct 单号,总金额,总已付款 from 表1
) a