表内字段计算问题

Question

原有数据表如下：
账号，收入金额，支出金额，日期
想通过SQL得到以下表：
账号，收入金额，支出金额，日期，余额
（金额=每笔记录的收入金额-支出金额+前一笔记录的余额）请高手指点。

Answer 1

select * ,
余额 = 收入金额-支出金额+(select top 1 收入金额-支出金额 from tb order by id desc)
from tb

Answer 2

select *,余额 = (select sum(收入金额)-sum(支出金额) from tb where 日期<=t.日期)
from tb t

Answer 3

表中没有余额字段，需要用统计完成。select * ,(select sum(收入金额)-sum(支出金额) from tb where where 日期<t.日期 ) from tb a

Answer 4

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-15 09:25:05.700●●●●●
　★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
  go
create table tb(id int,账号 nvarchar(30),收入金额 decimal(14,2),支出金额 decimal(14,2),日期 datetime) 
insert tb
select 
1,'53001',100,0,'2009-09-01'  union all select
2,'53001',300,0,'2009-09-02'  union all select
2,'53001',0,120,'2009-09-03'  union all select
2,'53001',300,0,'2009-09-04'  union all select
2,'53001',0,120,'2009-09-05'select a.账号,a.收入金额 as 收入金额,a.支出金额 as 支出金额,日期 日期,
       余额=a.收入金额-a.支出金额+isnull((select sum(收入金额)-sum(支出金额) 
         from tb where 账号=a.账号 and 日期<a.日期),0)
  from tb a
  
/*
账号 收入金额 支出金额 日期 余额
53001 100.00 0.00 2009-09-01 00:00:00.000 100.00
53001 300.00 0.00 2009-09-02 00:00:00.000 400.00
53001 0.00 120.00 2009-09-03 00:00:00.000 280.00
53001 300.00 0.00 2009-09-04 00:00:00.000 580.00
53001 0.00 120.00 2009-09-05 00:00:00.000 460.00
*/财务上最简单的一个问题！

Answer 5

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-15 09:31:06
--  Version: Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
May  3 2005 23:18:38 
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([账号] NVARCHAR(10),[收入金额] INT,[支出金额] INT,[日期] NVARCHAR(10))
INSERT [tb]
SELECT 'a',300,20,'090801' UNION ALL
SELECT 'a',0,60,'090802' UNION ALL
SELECT 'a',50,20,'090802' UNION ALL
SELECT 'b',300,20,'090803' UNION ALL
SELECT 'b',0,60,'090803' UNION ALL
SELECT 'b',50,20,'090804'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select identity(int) as id,* into # from tb order by 账号,日期
select *,余额 = (select sum(收入金额)-sum(支出金额) from # where 账号=t.账号 and id<=t.id)
from # t
drop table #
/*
id          账号         收入金额        支出金额        日期         余额          
----------- ---------- ----------- ----------- ---------- ----------- 
1           a          300         20          090801     280
2           a          0           60          090802     220
3           a          50          20          090802     250
4           b          300         20          090803     280
5           b          0           60          090803     220
6           b          50          20          090804     250（所影响的行数为 6 行）
*/

Answer 6

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-15 09:25:05.700●●●●●
　★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
  go
create table tb(id int,账号 nvarchar(30),收入金额 decimal(14,2),支出金额 decimal(14,2),日期 datetime) 
insert tb
select 
1,'53001',100,0,'2009-09-01'  union all select
2,'53001',300,0,'2009-09-02'  union all select
2,'53001',0,120,'2009-09-03'  union all select
2,'53001',300,0,'2009-09-04'  union all select
2,'53001',0,120,'2009-09-05'  union all select
1,'53002',100,0,'2009-09-01'  union all select
2,'53002',300,0,'2009-09-02'  union all select
2,'53002',0,120,'2009-09-03'  union all select
2,'53002',300,0,'2009-09-04'  union all select
2,'53002',0,120,'2009-09-05'--分开计
select a.账号,a.收入金额 as 收入金额,a.支出金额 as 支出金额,CONVERT(nvarchar(10),日期,120) 日期,
       余额=a.收入金额-a.支出金额+isnull((select sum(收入金额)-sum(支出金额) 
         from tb where 账号=a.账号 and 日期<a.日期),0)
  from tb a  
/*
账号 收入金额 支出金额 日期 余额
53001 100.00 0.00 2009-09-01 100.00
53001 300.00 0.00 2009-09-02 400.00
53001 0.00 120.00 2009-09-03 280.00
53001 300.00 0.00 2009-09-04 580.00
53001 0.00 120.00 2009-09-05 460.00
53002 100.00 0.00 2009-09-01 100.00
53002 300.00 0.00 2009-09-02 400.00
53002 0.00 120.00 2009-09-03 280.00
53002 300.00 0.00 2009-09-04 580.00
53002 0.00 120.00 2009-09-05 460.00
*/其它用楼上高手们的方法

Answer 7

if object_id('tb')is not null drop table tb
go
create table tb(账号 int,收入金额 int,支出金额 int,日期 datetime)
insert tb select
12,4000,300,'2009-09-12' union all select
13,4000,300,'2009-09-12' union all select
12,4000,300,'2009-09-13' union all select
13,4000,300,'2009-09-13' union all select
12,4000,300,'2009-09-14' union all select
13,4000,300,'2009-09-14' select *,余额=(select sum(收入金额-支出金额) from tb where 账号=t.账号 and 日期<=t.日期) from tb t账号          收入金额        支出金额        日期                      余额
----------- ----------- ----------- ----------------------- -----------
12          4000        300         2009-09-12 00:00:00.000 3700
13          4000        300         2009-09-12 00:00:00.000 3700
12          4000        300         2009-09-13 00:00:00.000 7400
13          4000        300         2009-09-13 00:00:00.000 7400
12          4000        300         2009-09-14 00:00:00.000 11100
13          4000        300         2009-09-14 00:00:00.000 11100(6 行受影响)

Answer 8

再补充一下刚才的问题：
我是通过视图得到如下数据表的：
账户 收入金额 支出金额 日期
10010001 99 NULL 2008-1-1
10010002 NULL 19879 2009-7-1
10010001 NULL 123.4 2009-8-8
10010003 100000 NULL 2009-8-23
10010001 43450 NULL 2009-8-25
10010001 12345 NULL 2009-9-1
10010001 NULL 12345 2009-9-1
表中的日期会有重复的，这样通过日期来比较计算的结果就不对了，有没有方法在生成视图时再产生一个唯一的标识字段，然后通过比较该字段来计算余额呢？

Answer 9

恩，tony考虑的很周全，正是我要的。谢谢各位！结贴ing

Answer 10

tony，有没有办法不修改原有的表结构呢？是否有办法可以直接在视图中新增一个表示字段？

Answer 11

SQL2005的可以直接用row_number()函数,SQL2000的要用自增列属性.