问题描叙:我口袋里有钱,有多少不知道,现在我会有收入,支出,从某一时刻开始算起收入表: in_date, in_money 之出表: out_date,out_money
in_date,out_date为时间型,若有相等时刻出现以IN_DATE为先。假设没有信用卡 (提前消费)时间 收入 支出 口袋最少结余
-----------------------------------
3-1 100 100
3-2 300 400
3-5 200 200
3-6 200 400
3-8 800 0
3-10 100 0
3-11 400 400大家看明白了吗,想得到这样的结果,但用游标会很慢,不用游标能实现吗?谢谢!
in_date,out_date为时间型,若有相等时刻出现以IN_DATE为先。假设没有信用卡 (提前消费)时间 收入 支出 口袋最少结余
-----------------------------------
3-1 100 100
3-2 300 400
3-5 200 200
3-6 200 400
3-8 800 0
3-10 100 0
3-11 400 400大家看明白了吗,想得到这样的结果,但用游标会很慢,不用游标能实现吗?谢谢!
with tb(a,b)as(
select '2013-3-1',100 union all
select '2013-3-2',300 union all
select '2013-3-6',200 union all
select '2013-3-11',400
)
,tc(a,b)as(
select '2013-3-5',200 union all
select '2013-3-8',800 union all
select '2013-3-10',100
)
,td as(
select CONVERT(datetime,a)a,b from tb
union all
select CONVERT(datetime,a),b*-1 from tc
),te as(
select b.a,(case when SUM(a.b)<0 then 0 else sum(a.b) end) b from td a,td b
where a.a<=b.a
group by b.a
)
select te.a 时间,tb.b 收入,tc.b 支出,isnull((case when te.b=0 and tb.b>0 then tb.b
when te.b>0 then te.b end),0) 口袋最少余额
from te left join tb on te.a=CONVERT(datetime,tb.a)
left join tc on te.a=CONVERT(datetime,tc.a)
时间字段如果是你的那种不好排序...如果非用你那个你想个排序的招..
比如在后面再加一行收入100 那么口袋应有500,而按你的程序则为100时间 收入 支出 口袋最少结余
-----------------------------------
3-1 100 100
3-2 300 400
3-5 200 200
3-6 200 400
3-8 800 0
3-10 100 0
3-11 400 400
3-13 100 500我把你的代码转成了sqlserver ,如下:select * into #temp_tb from (
select '2013-3-1' a,100 b union all
select '2013-3-2',300 union all
select '2013-3-6',200 union all
select '2013-3-11',400 union all
select '2013-3-13',100
) c
select * into #temp_tc from (
select '2013-3-5'a,200 b union all
select '2013-3-8',800 union all
select '2013-3-10',100
) c/*
select * from #temp_tb
select * from #temp_tcdrop table #temp_tb
drop table #temp_tc
*/
select CONVERT(datetime,a)a,b into #temp_td from #temp_tb
union all
select CONVERT(datetime,a),b*-1 from #temp_tc
select b.a,(case when sum(a.b)<0 then 0 else sum(a.b) end) b into #temp_te from #temp_td a,#temp_td b
where a.a<=b.a
group by b.a
/*
select * from #temp_tb
select * from #temp_tcselect * from #temp_td
select * from #temp_te
*/select e.a 时间,b.b 收入,c.b 支出,isnull((case when e.b=0 and b.b>0 then b.b
when e.b>0 then e.b end),0) 口袋最少余额
from #temp_te e left join #temp_tb b on e.a=CONVERT(datetime,b.a)
left join #temp_tc c on e.a=CONVERT(datetime,c.a) drop table #temp_tb
drop table #temp_tc
drop table #temp_td
drop table #temp_te
select * into #InMoeny from (
select '2013-3-1' a,100 b union all
select '2013-3-2',300 union all
select '2013-3-5',200union all
select '2013-3-6',200 union all
select '2013-3-8',800 union all
select '2013-3-10',100 union all
select '2013-3-11',400
) cselect * into #OutMoeny from (
select '2013-3-1' a,100 b union all
select '2013-3-2',400 union all
select '2013-3-5',200 union all
select '2013-3-6',400 union all
select '2013-3-11',400
) cdrop table #OutMoeny
drop table #InMoeny
select * from #InMoeny
union all
select * from #OutMoenyselect cast(t.a as datetime)'日期',SUM( case when t.FType='In' then t.b else 0 end) '收入',
sum(case when t.FType='Out' then t.b else 0 end) '支出'
from
(select a,b,'In' FType from #InMoeny
union all
select a,b,'Out' FType from #OutMoeny) T
group by t.a order by cast(t.a as datetime)
2013-03-01 00:00:00.000 100 100
2013-03-02 00:00:00.000 300 400
2013-03-05 00:00:00.000 200 200
2013-03-06 00:00:00.000 200 400
2013-03-08 00:00:00.000 800 0
2013-03-10 00:00:00.000 100 0
2013-03-11 00:00:00.000 400 400
结果为,有问题吗?日期 收入 支出
2013-3-1 100 100
2013-3-2 300 400
2013-3-5 200 200
2013-3-6 200 400
2013-3-8 800 0
2013-3-10 100 0
2013-3-11 400 400
2013-3-12 150 0
select * into #temp_tb from (
select '2013-3-1' a,100 b union all
select '2013-3-2',300 union all
select '2013-3-6',200 union all
select '2013-3-11',400 union all
select '2013-3-13',100
) c
select * into #temp_tc from (
select '2013-3-5'a,200 b union all
select '2013-3-8',800 union all
select '2013-3-10',100
) c
select row_number() over(order by c.日期 asc) as 'rowd',c.日期,c.收入,c.支出,c.收入-c.支出 as '结余' into #c from(
select CONVERT(datetime,a) '日期',b '收入',0 '支出'from #temp_tb
union all
select CONVERT(datetime,a) '日期',0 '收入',b '支出' from #temp_tc
)c
order by 日期 ascselect c1.rowd,c1.日期,c1.收入,-c1.支出 as '--支出',c1.结余+isnull(c2.结余,0) as '余额' from #c C1 left join #c C2
on c1.rowd=c2.rowd+1
结果:
rowd 日期 收入 --支出 余额
-------------------- ----------------------- ----------- ----------- -----------
1 2013-03-01 00:00:00.000 100 0 100
2 2013-03-02 00:00:00.000 300 0 400
3 2013-03-05 00:00:00.000 0 -200 100
4 2013-03-06 00:00:00.000 200 0 0
5 2013-03-08 00:00:00.000 0 -800 -600
6 2013-03-10 00:00:00.000 0 -100 -900
7 2013-03-11 00:00:00.000 400 0 300
8 2013-03-13 00:00:00.000 100 0 500
select '2013-3-1',100 union all
select '2013-3-2',300 union all
select '2013-3-6',200 union all
select '2013-3-11',400 union all
select '2013-3-12',100 union all
select '2013-3-14',100 union all
select '2013-3-17',100 union all
select '2013-3-18',100
)
,tc(a,b)as(
select '2013-3-5',200 union all
select '2013-3-8',800 union all
select '2013-3-10',100 union all
select '2013-3-13',1000 union all
select '2013-3-15',50 union all
select '2013-3-16',100
)
,td as(
select CONVERT(datetime,a)a,b from tb
union all
select CONVERT(datetime,a),b*-1 from tc
)
select number=row_number() over(order by a),* into #t from td order by 1
go
declare @a int
declare @b int
declare @tab table(a int,b datetime,c int, d int)
insert into @tab(a,b,c) select * from #t
set @a=1
set @b=0
while @a<=(select count(1) from @tab)
begin
if(select sum(c) from @tab where a between @b+1 and @a)>=0
begin
update @tab set d=(select sum(c) from @tab where a between @b+1 and @a) where a=@a
set @a=@a+1
end
else
begin
update @tab set d=0 where a=@a
set @b=@a
set @a=@a+1
end
end
select
时间=b,
收入=(select c from @tab where b=a.b and c>0),
支出=(select c*-1 from @tab where b=a.b and c<0),
口袋最少结余=d from @tab a
go
drop table #t
时间 收入 支出 口袋最少结余
-----------------------------------
3-1 100 100
3-2 300 400
3-5 200 200
3-6 200 400
3-8 800 0 <----不用足额支付?差的400哪里去了?负值变为0?
3-10 100 0 <----假设 没 有信用卡(提前消费),收支不平衡啊,提前消费了却不记录这个消费
3-11 400 400
3-13 100 500
declare @sums int,@i int
declare @datess datetime
set @i=0
select @sums=COUNT(*) from #c
while @i<@sums
begin
set @i+=1
SELECT @datess=日期 FROM #C
WHERE ROWD=@i
select @i'记录号',@datess '日期',case when SUM(d.结余)<0 then 0 else SUM(d.结余) end '口袋现金' from(
select top (@i) C.日期,结余 from #c c
order by 日期 ASC)d
END暂无法做到中间有结果负值归零。去使用游标吧。
太难搞,瞎搞一通,见笑了。PS:楼主不会欠钱不还吧~娱乐一下
时间 收入 支出 口袋最少结余 -----------------------------------
3-1 100 100
3-2 300 400
3-5 200 200
3-6 200 400
3-8 800 0 <----不用足额支付?差的400哪里去了?负值变为0?
3-10 100 0 <----假设 没 有信用卡(提前消费),收支不平衡啊,提前消费了却 3-11 400 400 不记录这个消费
3-13 100 500 我之前描叙问题的时候说了,”我口袋里有钱,有多少不知道“
所以当我花800时,说明原来口袋里有再收入400时,口袋里至少有400,或者多余400 ,不会刷信用卡!