首页 > 数据库技术 > 详细

SQL 流水账余额查询

时间:2017-10-29 00:23:46      阅读:435      评论:0      收藏:0      [点我收藏+]

创建表

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [RQ] [date] NULL,
    [SR] [int] NULL,
    [ZC] [int] NULL
) ON [PRIMARY]

插入数据

INSERT INTO [demo].[dbo].[test]([id],[RQ],[SR],[ZC])
VALUES(1,2017-10-01 00:00:00,30,NULL);
INSERT INTO [demo].[dbo].[test]([id],[RQ],[SR],[ZC])
VALUES(2,2017-10-02 00:00:00,NULL,20);
INSERT INTO [demo].[dbo].[test]([id],[RQ],[SR],[ZC])
VALUES(3,2017-10-03 00:00:00,20,NULL);
INSERT INTO [demo].[dbo].[test]([id],[RQ],[SR],[ZC])
VALUES(4,2017-10-04 00:00:00,NULL,10);
INSERT INTO [demo].[dbo].[test]([id],[RQ],[SR],[ZC])
VALUES(5,2017-10-06 00:00:00,40,NULL);

日期字段不重复

SELECT t.RQ, t.SR, t.ZC, (
           SELECT SUM(SR) -SUM(ZC)
           FROM   test
           WHERE  rq <= t.rq
       )     AS 余额
FROM   test  AS t

日期不唯一, 用自增字段来区分:

SELECT t.ID,t.RQ, t.SR, t.ZC, (
           SELECT SUM(SR) -SUM(ZC)
           FROM   test
           WHERE  ID <= t.id
       )     AS 余额
FROM   test  AS t

 效果

技术分享

SQL 流水账余额查询

原文:http://www.cnblogs.com/win32pro/p/7748604.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!