select 日期,客户,流入,流出 ,流出-流入 as 结余 from ( select 日期, 客户,count(流入) as 流入 from tableA group by 日期, 客户 ) A( select 日期, 客户,count(流出) as 流出 from tableA group by 日期, 客户 )B where A.日期=B.日期 and A.客户=B.客户
insert into 流出业务数据表 select '2013-1-2', 'A', 50 union all select '2013-1-3', 'A', 100 -- 建存储过程 create proc sp_getHistoryList (@curDate varchar(12), @customer varchar(5)) as beginwith t as (select a.日期, @customer '客户', isnull(b.流入,0) '流入', isnull(c.流出,0) '流出' from (select convert(varchar,dateadd(d,number,cast(rtrim(year(@curDate))+'-1-1' as datetime)),23) '日期' from master.dbo.spt_values where type='P' and number<datediff(d,rtrim(year(@curDate))+'-1-1',@curDate)) a left join (select convert(varchar,cast(日期 as date),23) '日期',sum(流入) '流入' from 流入业务数据表 where 客户=@customer group by convert(varchar,cast(日期 as date),23)) b on a.日期=b.日期 left join (select convert(varchar,cast(日期 as date),23) '日期',sum(流出) '流出' from 流出业务数据表 where 客户=@customer group by convert(varchar,cast(日期 as date),23)) c on a.日期=c.日期), u as (select a.日期,a.客户, isnull((select c.期初 from 期初数据表 c where c.年度=rtrim(year(@curDate)) and c.客户=a.客户),0) +isnull((select sum(b.流入)-sum(b.流出) from t b where b.日期<a.日期),0) '期初', a.流入,a.流出 from t a) select 日期,客户,期初,流入,流出,期初+流入-流出 '本日结存' from uend -- 测试1 exec sp_getHistoryList @curDate='2013-1-5',@customer='A';/* 日期 客户 期初 流入 流出 本日结存 ------------------------------ ----- ----------- ----------- ----------- ----------- 2013-01-01 A 1000 0 0 1000 2013-01-02 A 1000 100 50 1050 2013-01-03 A 1050 0 100 950 2013-01-04 A 950 200 0 1150(4 row(s) affected) */ -- 测试2 exec sp_getHistoryList @curDate='2013-1-5',@customer='B';/* 日期 客户 期初 流入 流出 本日结存 ------------------------------ ----- ----------- ----------- ----------- ----------- 2013-01-01 B 0 300 0 300 2013-01-02 B 300 0 0 300 2013-01-03 B 300 0 0 300 2013-01-04 B 300 0 0 300(4 row(s) affected) */
初步写了一下 create proc pro_SelectMoney @selectName varchar(100), @selectDate datetime as begin declare @str varchar(1000) declare @days int declare @i int declare @TotolMoney decimal declare @InMoney decimal declare @OutMoney decimal declare @qichu decimal declare @Kehu varchar(10) declare @riqi varchar(10) set @i=1 select @days=DATEPART(dd,@selectDate) create table #tab(日期 varchar(30),客户 varchar(30),期初 int,流入 int, 流出 int,本日结存 decimal) while(@i<@days) begin if((select COUNT(1) from 流入业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar)))>0 or (select COUNT(1) from 流出业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar)))>0 ) begin set @qichu=@TotolMoney select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0)) set @InMoney=0 set @OutMoney=0 set @i=@i+1 end else begin if(@TotolMoney>0) begin set @qichu=@TotolMoney select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0)) set @i=@i+1 set @InMoney=0 set @OutMoney=0 end else begin select @qichu=ISNULL(a.期初,0),@TotolMoney=ISNULL(a.期初,0) from 期初数据表 a where a.客户=@selectName insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),@TotolMoney) set @i=@i+1 end end end select * from #tab drop table #tab end
insert into 流出业务数据表 select '2013-1-2', 'A', 50 union all select '2013-1-3', 'A', 100 alter proc pro_SelectMoney @selectName varchar(100), @selectDate datetime as begin declare @str varchar(1000) declare @days int declare @i int declare @TotolMoney decimal declare @InMoney decimal declare @OutMoney decimal declare @qichu decimal declare @Kehu varchar(10) declare @riqi varchar(10) set @i=1 select @days=DATEPART(dd,@selectDate) create table #tab(日期 varchar(30),客户 varchar(30),期初 int,流入 int, 流出 int,本日结存 decimal) while(@i<@days) begin if((select COUNT(1) from 流入业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar)))>0 or (select COUNT(1) from 流出业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar)))>0 ) begin set @qichu=@TotolMoney select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0)) set @InMoney=0 set @OutMoney=0 set @i=@i+1 end else begin if(@TotolMoney>0) begin set @qichu=@TotolMoney select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0)) set @i=@i+1 set @InMoney=0 set @OutMoney=0 end else begin select @qichu=ISNULL(a.期初,0),@TotolMoney=ISNULL(a.期初,0) from 期初数据表 a where a.客户=@selectName insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))), isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),@TotolMoney) set @i=@i+1 end end end select * from #tab -- drop table #tab end
select 日期,客户,流入,流出 ,流出-流入 as 结余
from
(
select 日期, 客户,count(流入) as 流入 from tableA
group by 日期, 客户
) A(
select 日期, 客户,count(流出) as 流出 from tableA
group by 日期, 客户
)B
where A.日期=B.日期 and A.客户=B.客户
晕了,count sum不分了……
create table 期初数据表
(年度 varchar(6), 客户 varchar(5), 期初 int)
insert into 期初数据表
select '2013', 'A', 1000
create table 流入业务数据表
(日期 varchar(12), 客户 varchar(5), 流入 int)
insert into 流入业务数据表
select '2013-1-2', 'A', 100 union all
select '2013-1-4', 'A', 200 union all
select '2013-1-1', 'B', 300
create table 流出业务数据表
(日期 varchar(12), 客户 varchar(5), 流出 int)
insert into 流出业务数据表
select '2013-1-2', 'A', 50 union all
select '2013-1-3', 'A', 100
-- 建存储过程
create proc sp_getHistoryList
(@curDate varchar(12),
@customer varchar(5))
as
beginwith t as
(select a.日期,
@customer '客户',
isnull(b.流入,0) '流入',
isnull(c.流出,0) '流出'
from
(select convert(varchar,dateadd(d,number,cast(rtrim(year(@curDate))+'-1-1' as datetime)),23) '日期'
from master.dbo.spt_values
where type='P' and number<datediff(d,rtrim(year(@curDate))+'-1-1',@curDate)) a
left join
(select convert(varchar,cast(日期 as date),23) '日期',sum(流入) '流入'
from 流入业务数据表 where 客户=@customer
group by convert(varchar,cast(日期 as date),23)) b on a.日期=b.日期
left join
(select convert(varchar,cast(日期 as date),23) '日期',sum(流出) '流出'
from 流出业务数据表 where 客户=@customer
group by convert(varchar,cast(日期 as date),23)) c on a.日期=c.日期),
u as
(select a.日期,a.客户,
isnull((select c.期初 from 期初数据表 c
where c.年度=rtrim(year(@curDate)) and c.客户=a.客户),0)
+isnull((select sum(b.流入)-sum(b.流出) from t b
where b.日期<a.日期),0) '期初',
a.流入,a.流出
from t a)
select 日期,客户,期初,流入,流出,期初+流入-流出 '本日结存'
from uend
-- 测试1
exec sp_getHistoryList @curDate='2013-1-5',@customer='A';/*
日期 客户 期初 流入 流出 本日结存
------------------------------ ----- ----------- ----------- ----------- -----------
2013-01-01 A 1000 0 0 1000
2013-01-02 A 1000 100 50 1050
2013-01-03 A 1050 0 100 950
2013-01-04 A 950 200 0 1150(4 row(s) affected)
*/
-- 测试2
exec sp_getHistoryList @curDate='2013-1-5',@customer='B';/*
日期 客户 期初 流入 流出 本日结存
------------------------------ ----- ----------- ----------- ----------- -----------
2013-01-01 B 0 300 0 300
2013-01-02 B 300 0 0 300
2013-01-03 B 300 0 0 300
2013-01-04 B 300 0 0 300(4 row(s) affected)
*/
@selectName varchar(100),
@selectDate datetime
as
begin
declare @str varchar(1000)
declare @days int
declare @i int
declare @TotolMoney decimal
declare @InMoney decimal
declare @OutMoney decimal
declare @qichu decimal
declare @Kehu varchar(10)
declare @riqi varchar(10)
set @i=1
select @days=DATEPART(dd,@selectDate)
create table #tab(日期 varchar(30),客户 varchar(30),期初 int,流入 int, 流出 int,本日结存 decimal)
while(@i<@days)
begin
if((select COUNT(1) from 流入业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar)))>0
or (select COUNT(1) from 流出业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar)))>0
)
begin
set @qichu=@TotolMoney
select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0))
set @InMoney=0
set @OutMoney=0
set @i=@i+1
end
else
begin
if(@TotolMoney>0)
begin
set @qichu=@TotolMoney
select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0))
set @i=@i+1
set @InMoney=0
set @OutMoney=0
end
else
begin
select @qichu=ISNULL(a.期初,0),@TotolMoney=ISNULL(a.期初,0) from 期初数据表 a
where a.客户=@selectName
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),@TotolMoney)
set @i=@i+1
end
end
end
select * from #tab
drop table #tab
end
exec pro_SelectMoney 'B','2013-1-5'
create table 期初数据表
(年度 varchar(6), 客户 varchar(5), 期初 int)
insert into 期初数据表
select '2013', 'A', 1000
create table 流入业务数据表
(日期 varchar(12), 客户 varchar(5), 流入 int)
insert into 流入业务数据表
select '2013-1-2', 'A', 100 union all
select '2013-1-4', 'A', 200 union all
select '2013-1-1', 'B', 300
create table 流出业务数据表
(日期 varchar(12), 客户 varchar(5), 流出 int)
insert into 流出业务数据表
select '2013-1-2', 'A', 50 union all
select '2013-1-3', 'A', 100
alter proc pro_SelectMoney
@selectName varchar(100),
@selectDate datetime
as
begin
declare @str varchar(1000)
declare @days int
declare @i int
declare @TotolMoney decimal
declare @InMoney decimal
declare @OutMoney decimal
declare @qichu decimal
declare @Kehu varchar(10)
declare @riqi varchar(10)
set @i=1
select @days=DATEPART(dd,@selectDate)
create table #tab(日期 varchar(30),客户 varchar(30),期初 int,流入 int, 流出 int,本日结存 decimal)
while(@i<@days)
begin
if((select COUNT(1) from 流入业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar)))>0
or (select COUNT(1) from 流出业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar)))>0
)
begin
set @qichu=@TotolMoney
select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0))
set @InMoney=0
set @OutMoney=0
set @i=@i+1
end
else
begin
if(@TotolMoney>0)
begin
set @qichu=@TotolMoney
select @Kehu=a.客户,@InMoney=ISNULL(a.流入,0) from 流入业务数据表 a where a.客户=@selectName and a.日期=('2013-1-'+cast(@i as varchar))
select @OutMoney=ISNULL(b.流出,0) from 流出业务数据表 b where b.客户=@selectName and b.日期=('2013-1-'+cast(@i as varchar))
set @TotolMoney=isnull(@TotolMoney,0)+isnull(@InMoney,0)-isnull(@OutMoney,0)
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),isnull(@TotolMoney,0))
set @i=@i+1
set @InMoney=0
set @OutMoney=0
end
else
begin
select @qichu=ISNULL(a.期初,0),@TotolMoney=ISNULL(a.期初,0) from 期初数据表 a
where a.客户=@selectName
insert into #tab values (isnull(@riqi,('2013-1-'+cast(@i as varchar))),
isnull(@Kehu,@selectName),isnull(@qichu,0),isnull(@InMoney,0),isnull(@OutMoney,0),@TotolMoney)
set @i=@i+1
end
end
end
select * from #tab
-- drop table #tab
end
exec pro_SelectMoney 'A','2013-1-5'
晕了,count sum不分了……
使用这个方式,客户跑一年的数据,跑了24小时没有跑完。
最后说服客户不用每天,按照你另一个贴回复的方法
http://bbs.csdn.net/topics/390486274
结果有误,这要怎么改?