如下查询请教怎么写？（可能比较简单，但是时间急啊，先行谢了）
表A：
客户名 预付款 预付日期
--------------------------------
张三 10000 2007-09-01
李四 20000 2007-09-01
张三 20000 2007-09-02
张三 40000 2007-09-04
张三 50000 2007-09-05
表B：
客户名 消费款 消费日期
--------------------------------
张三 1000 2007-09-01 08:00:00
张三 500 2007-09-01 14:00:0
李四 20000 2007-09-03 12:00:00
张三 10000 2007-09-04 12:00:01
张三 10000 2007-09-04 13:00:00
张三 90000 2007-09-06 09:00:00
张三 10000 2007-09-07 10:00:00
如何能得到如下的查询报表：(其实预付款日期与日期一致可以不要的）
如查询客户“张三”的9月份“结存余款报表 "
日期预付款预付款日期消费金额 结存欠
---------------------------
110000 2007-09-01 15008500
2200002007-09-02028500
4400002007-09-042000048500
5500002007-09-05098500
602007-09-06900008500
702007-09-0710000-1500
sum 120000121500-1500
[解决办法]
create table A(客户名 varchar(10),预付款 int,预付日期 datetime)
insert into A values( '张三 ', 10000, '2007-09-01 ')
insert into A values( '李四 ', 20000, '2007-09-01 ')
insert into A values( '张三 ', 20000, '2007-09-02 ')
insert into A values( '张三 ', 40000, '2007-09-04 ')
insert into A values( '张三 ', 50000, '2007-09-05 ')
create table B(客户名 varchar(10),消费款 int,消费日期 datetime)
insert into B values( '张三 ', 1000, '2007-09-01 08:00:00 ')
insert into B values( '张三 ', 500, '2007-09-01 14:00:0 ')
insert into B values( '李四 ', 20000, '2007-09-03 12:00:00 ')
insert into B values( '张三 ', 10000, '2007-09-04 12:00:01 ')
insert into B values( '张三 ', 10000, '2007-09-04 13:00:00 ')
insert into B values( '张三 ', 90000, '2007-09-06 09:00:00 ')
insert into B values( '张三 ', 10000, '2007-09-07 10:00:00 ')
go
declare @客户名 as varchar(10)
set @客户名 = '张三 '
declare @日期 as varchar(10)
set @日期 = '2007-09 '
select isnull(t1.预付款日期,t2.消费款日期) 预付款日期,isnull(t1.预付款,0) 预付款,isnull(消费款,0) 消费金额 , isnull(t1.预付款,0) - isnull(消费款,0) 结存欠 into temp from
(select convert(varchar(10),预付日期,120) 预付款日期,sum(预付款) 预付款 from A where 客户名=@客户名 and left(convert(varchar(10),预付日期,120),7) = @日期 group by convert(varchar(10),预付日期,120)) t1 full join
(select convert(varchar(10),消费日期,120) 消费款日期,sum(消费款) 消费款 from B where 客户名=@客户名 and left(convert(varchar(10),消费日期,120),7) = @日期 group by convert(varchar(10),消费日期,120)) t2 on t1.预付款日期 = t2.消费款日期
update temp
set 结存欠=(select isnull(sum(预付款 - 消费金额),0)
from temp tt
where TT.预付款日期 <=temp.预付款日期)
select * from temp
drop table A,B,temp
/*
预付款日期 预付款 消费金额 结存欠
---------- ----------- ----------- -----------
2007-09-01 10000 1500 8500
2007-09-02 20000 0 28500
2007-09-04 40000 20000 48500
2007-09-05 50000 0 98500
2007-09-06 0 90000 8500
2007-09-07 0 10000 -1500
（所影响的行数为 6 行）
*/
[解决办法]
--
create table qq(客户名 varchar(100),预付款 int, 预付日期 varchar(100))
insert into qq(客户名,预付款, 预付日期)
select '张三 ', '10000 ', '2007-09-01 ' union all
select '李四 ', '20000 ', '2007-09-01 ' union all
select '张三 ' , '20000 ', '2007-09-02 'union all
select '张三 ' , '40000 ' , '2007-09-04 'union all
select '张三 ' , '50000 ' , '2007-09-05 '
create table qqq(客户名 varchar(100),消费款 int, 消费日期 varchar(100))
insert into qqq(客户名,消费款, 消费日期)
select '张三 ' , '1000 ' , '2007-09-01 08:00:00 ' union all
select '张三 ' , '500 ' , '2007-09-01 14:00:0 ' union all
select '李四 ' , '20000 ' , '2007-09-03 12:00:00 ' union all
select '张三 ' , '10000 ' , '2007-09-04 12:00:01 ' union all
select '张三 ' , '10000 ' , '2007-09-04 13:00:00 ' union all
select '张三 ' , '90000 ' , '2007-09-06 09:00:00 ' union all
select '张三 ' , '10000 ' , '2007-09-07 10:00:00 '
--
select id=(select count(*) from (select 预付款=isnull(预付款,0), 预付款日期=isnull(预付日期,消费日期),消费金额=isnull(消费金额,0) from
((select 预付款,预付日期 from qq where 客户名= '张三 ')a full join (select 消费日期=convert(varchar(10),消费日期,120),消费金额=sum(消费款) from qqq
where 客户名= '张三 ' group by convert(varchar(10),消费日期,120))b on a.预付日期=b.消费日期))e where e.预付款日期 <=d.预付款日期),
日期=day(预付款日期),
*,
结存欠=(select (sum(预付款)-sum(消费金额)) from (select 预付款=isnull(预付款,0), 预付款日期=isnull(预付日期,消费日期),消费金额=isnull(消费金额,0) from
((select 预付款,预付日期 from qq where 客户名= '张三 ')a full join (select 消费日期=convert(varchar(10),消费日期,120),消费金额=sum(消费款) from qqq
where 客户名= '张三 ' group by convert(varchar(10),消费日期,120))b on a.预付日期=b.消费日期))c where c.预付款日期 <=d.预付款日期) from
(select 预付款=isnull(预付款,0), 预付款日期=isnull(预付日期,消费日期),消费金额=isnull(消费金额,0) from
((select 预付款,预付日期 from qq where 客户名= '张三 ')a full join (select 消费日期=convert(varchar(10),消费日期,120),消费金额=sum(消费款) from qqq
where 客户名= '张三 ' group by convert(varchar(10),消费日期,120))b on a.预付日期=b.消费日期))d
--
id 日期 预付款 预付款日期 消费金额 结存欠
11100002007-09-0115008500
22200002007-09-02028500
34400002007-09-042000048500
45500002007-09-05098500
5602007-09-06900008500
6702007-09-0710000-1500
[解决办法]
/*
Limpire：我上面搞复杂了
*/
--原始数据表：@A
declare @A table(客户名 varchar(4),预付款 int,预付日期 datetime)
insert @A
select '张三 ',10000, '2007-09-01 ' union all
select '李四 ',20000, '2007-09-01 ' union all
select '张三 ',20000, '2007-09-02 ' union all
select '张三 ',40000, '2007-09-04 ' union all
select '张三 ',50000, '2007-09-05 '
--原始数据表：@B
declare @B table(客户名 varchar(4),消费款 int,消费日期 datetime)
insert @B
select '张三 ',1000, '2007-09-01 08:00:00 ' union all
select '张三 ',500, '2007-09-01 14:00:0 ' union all
select '李四 ',20000, '2007-09-03 12:00:00 ' union all
select '张三 ',10000, '2007-09-04 12:00:01 ' union all
select '张三 ',10000, '2007-09-04 13:00:00 ' union all
select '张三 ',90000, '2007-09-06 09:00:00 ' union all
select '张三 ',10000, '2007-09-07 10:00:00 '
declare @YM varchar(6) --查询范围
set @YM= '200709 '
--数据报表：@Report
declare @Report table(序号 int,客户名 varchar(4),预付款 int,预付日期 varchar(10),消费金额 int,结存欠 int)
--预付款和消费金额数据
insert @Report
select null,isnull(a.客户名,b.客户名),isnull(a.预付款,0),isnull(a.预付日期,b.消费日期),isnull(b.消费款,0),null
from
(select 客户名,预付款=sum(预付款),预付日期=convert(varchar(10),预付日期,120) from @A where convert(varchar(6),预付日期,112)=@YM group by 客户名,convert(varchar(10),预付日期,120)) a
full join
(select 客户名,消费款=sum(消费款),消费日期=convert(varchar(10),消费日期,120) from @B where convert(varchar(6),消费日期,112)=@YM group by 客户名, convert(varchar(10),消费日期,120)) b
on a.客户名=b.客户名 and convert(varchar(10),a.预付日期,120)=b.消费日期
--明细序号和结存欠数据
update @Report
set
序号=(select count(*) from @Report where 客户名=a.客户名 and 预付日期 <=a.预付日期),
结存欠=(select sum(预付款-消费金额) from @Report where 客户名=a.客户名 and 预付日期 <=a.预付日期)
from @Report a
--明细报表
select * from @Report order by 客户名,序号
/*
序号客户名预付款预付日期消费金额结存欠
1李四200002007-09-01020000
2李四02007-09-03200000
1张三100002007-09-0115008500
2张三200002007-09-02028500
3张三400002007-09-042000048500
4张三500002007-09-05098500
5张三02007-09-06900008500
6张三02007-09-0710000-1500
*/
--汇总报表
select 客户名,预付款=sum(预付款),消费金额=sum(消费金额),结存欠=sum(预付款-消费金额) from @Report group by 客户名 order by 客户名
/*
客户名预付款消费金额结存欠
李四20000200000
张三120000121500-1500
*/
