两个表的应收款问题,要求查询速度要快,高手请进!在线等

Question

二月以前欠费的没考虑 先写个简单的select HouseID(房号),
       sum(case when type = 1 then 金额 else 0 end) [2月应收],
       sum(case when type = -1 then 金额 else 0 end) [2月已收],
       sum(金额*type) [2月结余]
(select HouseID(房号),  PayDate (应收日期) as date, payValue(应收额) as 金额 ,costItem(费用类型) ,coststart(费用开始), costClose(费用截止) ,type = 1
from 应收表 
union all
select HouseID(房号),costDate (收款日期)  ,costValue(收款额) ,costItem(费用类型) ,coststart(费用开始) ,costClose(费用截止) ,type = -1
from 收款表 )T where costClose <='2009-02-10' and convert(varchar(7),coststart,120) ='2009-02'
group by HouseID(房号)

Answer 1

if object_id('[应收表]') is not null drop table [应收表]
go
create table [应收表]([PactID] int,[HouseID] varchar(3),[PayDate] datetime,[payValue] int,[costItem] varchar(4),[coststart] datetime,[costClose] varchar(10))
insert [应收表]
select 1,'A01','2009-01-01',5000,'租金','2009-01-01','2009-01-31' union all
select 1,'A01','2009-02-01',5000,'租金','2009-02-01','2009-02-28' union all
select 1,'A01','2009-03-01',5000,'租金','2009-03-01','2009-03-31' union all
select 1,'A01','2009-04-01',5000,'租金','2009-04-01','2009-04-31' union all
select 2,'A02','2009-01-15',8000,'租金','2009-01-15','2009-04-14'
go
if object_id('[收款表]') is not null drop table [收款表]
go
create table [收款表]([SCID] int,[HouseID] varchar(3),[costDate] datetime,[costValue] int,[costItem] varchar(4),[coststart] datetime,[costClose] varchar(10))
insert [收款表]
select 1,'A01','2009-01-08',2000,'租金','2009-01-01','2009-01-31' union all
select 2,'A01','2009-01-15',3000,'租金','2009-01-01','2009-01-31' union all
select 3,'A01','2009-02-01',4000,'租金','2009-02-01','2009-02-28' union all
select 4,'A01','2009-02-15',1000,'租金','2009-02-01','2009-02-28' union all
select 5,'A01','2009-03-01',5000,'租金','2009-03-01','2009-03-31' union all
select 6,'A01','2009-04-01',5000,'租金','2009-04-01','2009-04-31'
go
--select * from [应收表]
--select * from [收款表]
declare @dt datetime
set @dt='2009-02-10'
--set @dt='2009-02-16'select  a.HouseID
,[2月前欠费]=a.[2月前总应收]-isnull(b.[2月前总已收],0)
,a.[2月应收]
,isnull(b.[2月已收],0) [2月已收]
,[2月结余]=a.[2月底总应收]-isnull(b.[2月底总已收],0)
from
(
select  HouseID,[2月应收]=sum(case when month(PayDate)=month(@dt) then payValue else 0 end)
,[2月前总应收]=sum(case when month(PayDate)<month(@dt) then payValue else 0 end)
,[2月底总应收]=sum(case when month(PayDate)<=month(@dt) then payValue else 0 end)
from 应收表
where year(PayDate)=year(@dt) and month(PayDate)<=month(@dt)
group by HouseID
) a
left join
(
select  HouseID,[2月已收]=sum(case when month(costDate)=month(@dt) and costDate<=@dt then costValue else 0 end)
,[2月前总已收]=sum(case when month(costDate)<month(@dt) then costValue else 0 end)
,[2月底总已收]=sum(case when month(costDate)<=month(@dt) and costDate<=@dt then costValue else 0 end)
from 收款表
where year(costDate)=year(@dt) and month(costDate)<=month(@dt)
group by HouseID
) b
on a.HouseID=b.HouseID--@dt='2009-02-10'
/*
HouseID 2月前欠费       2月应收        2月已收        2月结余
------- ----------- ----------- ----------- -----------
A01     0           5000        4000        1000
A02     8000        0           0           8000(2 行受影响)
*/--@dt='2009-02-16'
/*
HouseID 2月前欠费       2月应收        2月已收        2月结余
------- ----------- ----------- ----------- -----------
A01     0           5000        5000        0
A02     8000        0           0           8000(2 行受影响)
*/