SQL 余额解决办法

SQL 余额
谢谢大家，

SQL code

表t1账号 日期    收入 支出A1 2011/6/10 200 0A1 2012/6/11 0 100A2 2012/6/10 100 0A2 2012/6/11 100 0A1 2012/6/14 0   100  A1 2012/6/15 300 0表t2账号 日期      今日余额 A1 2011/6/10 A1 2012/6/11 A2 2012/6/10 A2 2012/6/11 A1 2012/6/14  A1 2012/6/15 

SQL code

------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2012-06-15 11:34:31-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[t1]if object_id('[t1]') is not null drop table [t1]go create table [t1]([账号] varchar(2),[日期] datetime,[收入] int,[支出] int)insert [t1]select 'A1','2011/6/10',200,0 union allselect 'A1','2012/6/11',0,100 union allselect 'A2','2012/6/10',100,0 union allselect 'A2','2012/6/11',100,0 union allselect 'A1','2012/6/14',0,100 union allselect 'A1','2012/6/15',300,0--> 测试数据：[t2]if object_id('[t2]') is not null drop table [t2]go create table [t2]([账号] varchar(2),[日期] datetime,[今日余额] sql_variant)insert [t2]select 'A1','2011/6/10',null union allselect 'A1','2012/6/11',null union allselect 'A2','2012/6/10',null union allselect 'A2','2012/6/11',null union allselect 'A1','2012/6/14',null union allselect 'A1','2012/6/15',null--------------开始查询--------------------------select a.账号,a.日期, isnull((select sum(收入-支出) from t1 where 账号=a.账号 and 日期<a.日期),0) as 今日余额from t1 a join t2 bon a.账号=b.账号 and a.日期=b.日期order by  日期----------------结果----------------------------/* 账号   日期                      今日余额---- ----------------------- -----------A1   2011-06-10 00:00:00.000 0A2   2012-06-10 00:00:00.000 0A2   2012-06-11 00:00:00.000 100A1   2012-06-11 00:00:00.000 200A1   2012-06-14 00:00:00.000 100A1   2012-06-15 00:00:00.000 0(6 行受影响)*/
[解决办法]
SQL code
--> 测试数据：[表t1]if object_id('[表t1]') is not null drop table [表t1]create table [表t1]([账号] varchar(2),[日期] datetime,[收入] int,[支出] int)goinsert [表t1]select 'A1','2011/6/10',200,0 union allselect 'A1','2012/6/11',0,100 union allselect 'A2','2012/6/10',100,0 union allselect 'A2','2012/6/11',100,0 union allselect 'A1','2012/6/14',0,100 union allselect 'A1','2012/6/15',300,0goselect     [账号],    [日期],    [收入],    [支出],    (select SUM(isnull([收入],0)-isnull([支出],0))        from [表t1] b where a.[账号]=b.[账号] and b.日期<=a.日期) as [今日余额],    isnull((select SUM(isnull([收入],0)-isnull([支出],0))        from [表t1] b where a.[账号]=b.[账号] and b.日期<a.日期),0) as [昨日余额]from     [表t1] aorder by     [账号],[日期]/*账号    日期    收入    支出    今日余额    昨日余额---------------------A1    2011-06-10 00:00:00.000    200    0    200    0A1    2012-06-11 00:00:00.000    0    100    100    200A1    2012-06-14 00:00:00.000    0    100    0    100A1    2012-06-15 00:00:00.000    300    0    300    0A2    2012-06-10 00:00:00.000    100    0    100    0A2    2012-06-11 00:00:00.000    100    0    200    100*/