求一个sql语句对我有点难度，表结构数据已经奉上，希望各位大牛帮忙

[低代码开发平台]

declare @t_Yingshou table (p_id int,p_orgName varchar(10),p_customerName varchar(10),p_Date datetime,p_Money decimal(10,2))
insert into @t_Yingshou
select 1,'Folyer', '台机', '2013-12-3', 1000 union all
select 2,'Folyer', '台机','2014-1-5',2000 union all
select 3,'Jeez','裕祥','2013-12-1',3000

--select * from @t_Yingshou
declare @t_Shou table (p_id int,p_Yingshouid int,p_Date datetime,p_YMoney decimal(10,2),p_Money decimal(10,2))
insert into @t_Shou
select 1, 1, '2013-12-3', 100,300 union all
select 2, 1, '2013-12-6', 0,100 union all
select 3, 1, '2014-1-6', 100,300 union all
select 4,2, '2014-1-5',100,200 union all
select 5,3, '2013-12-1',100,300
--select * from @t_Shou
-----sql表结构如上，求每月应收分析表

--结果如下：本期结余=上期结余+本月货款-本月收款-本月优惠
组织机构,年度,月份,客户,上期结余,本月货款,本月收款,本月优惠,本期结余
Folyer 2013 12 台机 0 1000 400 100 500
Folyer 2014 1 台机 500 2000 500 200 1800
Jeez 2013 12 裕祥 0 3000 300 100 2600

[低代码开发平台]

--有个应收表如下@t_Yingshou： ID,组织机构,客户,应收日期,应收金额 --有个收款表如下：@t_Shou ID,应收表ID,收款日期,折扣金额,收款金额 现在要得到如下查询结果,每月应收分析表。 求各位大神的sql 表结构测试数据如下： declare @t_Yingshou table (p_id int,p_orgName varchar(10),p_customerName varchar(10),p_Date datetime,p_Money decimal(10,2)) insert into @t_Yingshou select 1,'Folyer', '台机', '2013-12-3', 1000 union all select 2,'Folyer', '台机','2014-1-5',2000 union all select 3,'Jeez','裕祥','2013-12-1',3000 --select * from @t_Yingshou declare @t_Shou table (p_id int,p_Yingshouid int,p_Date datetime,p_YMoney decimal(10,2),p_Money decimal(10,2)) insert into @t_Shou select 1, 1, '2013-12-3', 100,300 union all select 2, 1, '2013-12-6', 0,100 union all select 3, 1, '2014-1-6', 100,300 union all select 4,2, '2014-1-5',100,200 union all select 5,3, '2013-12-1',100,300 --select * from @t_Shou -----sql表结构如上，求每月应收分析表 --结果如下：本期结余=上期结余+本月货款-本月收款-本月优惠 组织机构,年度,月份,客户,上期结余,本月货款,本月收款,本月优惠,本期结余 Folyer 2013 12 台机 0 1000 400 100 500 Folyer 2014 1 台机 500 2000 500 200 1800 Jeez 2013 12 裕祥 0 3000 300 100 2600

[低代码开发平台]

SQL2000环境

[阳泉酒家小当家]

这个能用吗：

declare @t_Yingshou table (p_id int,p_orgName varchar(10),p_customerName varchar(10),p_Date datetime,p_Money decimal(10,2)) 
 insert into @t_Yingshou 
 select 1,'Folyer', '台机', '2013-12-3', 1000 union all 
 select 2,'Folyer', '台机','2014-1-5',2000 union all 
 select 3,'Jeez','裕祥','2013-12-1',3000  

 --select * from @t_Yingshou
 declare @t_Shou table (p_id int,p_Yingshouid int,p_Date datetime,p_YMoney decimal(10,2),p_Money decimal(10,2)) 
 insert into @t_Shou 
 select 1, 1,  '2013-12-3', 100,300 union all 
 select 2, 1,  '2013-12-6', 0,100 union all 
 select 3, 1,  '2014-1-6', 100,300 union all 
 select 4,2, '2014-1-5',100,200 union all 
 select 5,3, '2013-12-1',100,300  
 --select * from @t_Shou


 select d.p_orgName,d.p_customerName,c.d2,sum(c.p_Money) as 本月货款,sum(c.p_YMoney) as 本月优惠,
sum(c.p_Money2) as 本月收款,sum(c.p_Money)-sum(c.p_YMoney)-sum(c.p_Money2) as 本期结余
into #tt
 from (
 select  b.p_Yingshouid,b.d as d2,SUM(a.p_Money) as p_Money, 
 SUM(b.p_YMoney) as p_YMoney,sum(b.p_Money) as p_Money2
 from @t_Yingshou a right join 
 (
 select p_Yingshouid,SUM(p_YMoney) as p_YMoney,sum(p_Money) as p_Money,
 convert(varchar(7),p_Date,120) as d
 from @t_Shou
 group by p_Yingshouid,convert(varchar(7),p_Date,120)
 ) b
 on a.p_id=b.p_Yingshouid and convert(varchar(7),a.p_Date,120)=b.d  
 group by b.p_Yingshouid,b.d
 ) c join @t_Yingshou d on c.p_Yingshouid=d.p_id
 group by 
 d.p_orgName,p_customerName,d2
 order by d.p_orgName,d2

 select a.p_orgName,a.p_customerName,a.d2,isnull(b.本期结余,0) as 上期结余,a.本月货款,a.本月优惠,a.本月收款,a.本期结余+isnull(b.本期结余,0) as 本期结余 from #tt  a left join #tt b
 on a.p_orgName=b.p_orgName and a.p_customerName=b.p_customerName
 and DATEADD(MONTH,-1,a.d2+'-01')=cast(b.d2+'-01' as datetime)
  drop table #tt