有点难度！求助查询一个银行账户交易的sql语句

低代码开发平台 2021-03-03 09:52:45
银行账户表jzBancAcount(主键ID,代码,名称,开户日期，期初金额,即时余额)

收款表：jzShoukuanBill(主键ID，收款日期，收款金额，银行账户ID)

付款表：jzFukuan(主键ID，付款日期，付款金额，银行账户ID)

转账表：jzZhuanzhang(主键ID,转账日期，转出银行账户，转入银行账户，转账金额)

求一个银行的交易流水按日期排序
结果如下：
交易序号（按流水增长从1开始），日期，账户代码，账户名称，之前金额 收款 付款 即时余额
...全文
705 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
Hello World, 2021-03-04
  • 打赏
  • 举报
 回复
先组成流水，再计算出各项的结余： 

CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DATETIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) ;
WITH LiuShui AS (SELECT A.*, B.收款日期, B.收款金额, CAST(0 AS INT) AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzShoukuanBill B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, B.转账金额, CAST(0 AS INT)
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转入银行账户ID
                 UNION ALL
                 SELECT A.*, B.收款日期, CAST(0 AS INT) 收款金额, B.收款金额 AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzFukuan B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, CAST(0 AS INT), B.转账金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转出银行账户ID),
     LiuShuiWithSeq AS (SELECT  *, ROW_NUMBER() OVER (PARTITION BY LiuShui.代码 ORDER BY LiuShui.收款日期) AS SEQ FROM LiuShui),
     Resut AS (SELECT   LiuShuiWithSeq.代码,
                        LiuShuiWithSeq.名称,
                        LiuShuiWithSeq.收款日期 时间,
                        LiuShuiWithSeq.期初金额,
                        LiuShuiWithSeq.收款金额,
                        LiuShuiWithSeq.支出金额,
                        LiuShuiWithSeq.即时余额 + LiuShuiWithSeq.收款金额 - LiuShuiWithSeq.支出金额 即时余额,
                        LiuShuiWithSeq.SEQ
               FROM     LiuShuiWithSeq
               WHERE    SEQ = 1
               UNION ALL
               SELECT   A.代码, A.名称, A.收款日期 时间, B.即时余额, A.收款金额, A.支出金额, B.即时余额 + A.收款金额 - A.支出金额 即时余额, A.SEQ
               FROM     LiuShuiWithSeq A
                        INNER JOIN Resut B ON A.代码 = B.代码 AND   A.SEQ = B.SEQ + 1)
SELECT  * FROM  Resut ORDER BY  Resut.代码, Resut.时间;

drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
RINK_1 2021-03-04
  • 打赏
  • 举报
 回复
引用 8 楼 富莱尔软件 的回复:
[quote=引用 4 楼 RINK_1 的回复:]


CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
 

INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
 

INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150)

 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DateTIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150)

WITH CTE
AS
(SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill
UNION ALL
SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan
UNION ALL
SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang
UNION ALL
SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang)


SELECT ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ,
A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额,
ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款
FROM CTE A
JOIN #jzBancAcount B ON A.银行账户ID=B.ID
CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.日期>日期) AS C
ORDER BY 银行账户ID,日期


drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
这个遇到两个日期一模一样的就会有问题，我测试数据稍微改下。 CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20) ,开户日期 Date,期初金额 int,即时余额 int) INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000) INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000) CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DateTIME,收款金额 int) INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500) INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300) INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100) INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250) INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350) CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DateTIME,收款金额 int) INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:00:00',50) INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30) INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:00:00',10) INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250) INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DateTIME,转账金额 int) INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250) INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) WITH CTE AS (SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill UNION ALL SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan UNION ALL SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang UNION ALL SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang) SELECT ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ, A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额, ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款 FROM CTE A JOIN #jzBancAcount B ON A.银行账户ID=B.ID CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.日期>日期) AS C ORDER BY 银行账户ID,日期 drop table #jzBancAcount drop table #jzShoukuanBill drop table #jzFukuan drop table #jzZhuanzhang [/quote] 那就一上来就排序 


WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ
FROM
(SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill
UNION ALL
SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan
UNION ALL
SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang
UNION ALL
SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang) AS A)


SELECT SEQ,
A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额,
ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款
FROM CTE A
JOIN #jzBancAcount B ON A.银行账户ID=B.ID
CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.SEQ>SEQ) AS C
ORDER BY 银行账户ID,日期
低代码开发平台 2021-03-04
  • 打赏
  • 举报
 回复
引用 4 楼 RINK_1 的回复:


CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
 

INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
 

INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150)

 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DateTIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150)

WITH CTE
AS
(SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill
UNION ALL
SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan
UNION ALL
SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang
UNION ALL
SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang)


SELECT ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ,
A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额,
ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款
FROM CTE A
JOIN #jzBancAcount B ON A.银行账户ID=B.ID
CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.日期>日期) AS C
ORDER BY 银行账户ID,日期


drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
这个遇到两个日期一模一样的就会有问题，我测试数据稍微改下。 CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20) ,开户日期 Date,期初金额 int,即时余额 int) INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000) INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000) CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DateTIME,收款金额 int) INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500) INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300) INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100) INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250) INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350) CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DateTIME,收款金额 int) INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:00:00',50) INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30) INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:00:00',10) INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250) INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DateTIME,转账金额 int) INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250) INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) WITH CTE AS (SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill UNION ALL SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan UNION ALL SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang UNION ALL SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang) SELECT ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ, A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额, ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款 FROM CTE A JOIN #jzBancAcount B ON A.银行账户ID=B.ID CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.日期>日期) AS C ORDER BY 银行账户ID,日期 drop table #jzBancAcount drop table #jzShoukuanBill drop table #jzFukuan drop table #jzZhuanzhang
Hello World, 2021-03-04
  • 打赏
  • 举报
 回复
引用 6 楼 富莱尔软件 的回复:
[quote=引用 3 楼 Hello World, 的回复:]先组成流水，再计算出各项的结余： 

CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DATETIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) ;
WITH LiuShui AS (SELECT A.*, B.收款日期, B.收款金额, CAST(0 AS INT) AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzShoukuanBill B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, B.转账金额, CAST(0 AS INT)
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转入银行账户ID
                 UNION ALL
                 SELECT A.*, B.收款日期, CAST(0 AS INT) 收款金额, B.收款金额 AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzFukuan B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, CAST(0 AS INT), B.转账金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转出银行账户ID),
     LiuShuiWithSeq AS (SELECT  *, ROW_NUMBER() OVER (PARTITION BY LiuShui.代码 ORDER BY LiuShui.收款日期) AS SEQ FROM LiuShui),
     Resut AS (SELECT   LiuShuiWithSeq.代码,
                        LiuShuiWithSeq.名称,
                        LiuShuiWithSeq.收款日期 时间,
                        LiuShuiWithSeq.期初金额,
                        LiuShuiWithSeq.收款金额,
                        LiuShuiWithSeq.支出金额,
                        LiuShuiWithSeq.即时余额 + LiuShuiWithSeq.收款金额 - LiuShuiWithSeq.支出金额 即时余额,
                        LiuShuiWithSeq.SEQ
               FROM     LiuShuiWithSeq
               WHERE    SEQ = 1
               UNION ALL
               SELECT   A.代码, A.名称, A.收款日期 时间, B.即时余额, A.收款金额, A.支出金额, B.即时余额 + A.收款金额 - A.支出金额 即时余额, A.SEQ
               FROM     LiuShuiWithSeq A
                        INNER JOIN Resut B ON A.代码 = B.代码 AND   A.SEQ = B.SEQ + 1)
SELECT  * FROM  Resut ORDER BY  Resut.代码, Resut.时间;

drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
这里换个数据类型就报错了！[/quote] 把类型整成一样的就可以了 

CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 DECIMAL(18,4),即时余额 DECIMAL(18,4))
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  DECIMAL(18,4))
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  DECIMAL(18,4))
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DATETIME,转账金额  DECIMAL(18,4))
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) ;
WITH LiuShui AS (SELECT A.*, B.收款日期, B.收款金额, CAST(0 AS DECIMAL(18,4)) AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzShoukuanBill B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, B.转账金额, CAST(0 AS DECIMAL(18,4))
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转入银行账户ID
                 UNION ALL
                 SELECT A.*, B.收款日期, CAST(0 AS DECIMAL(18,4)) 收款金额, B.收款金额 AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzFukuan B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, CAST(0 AS DECIMAL(18,4)), B.转账金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转出银行账户ID),
     LiuShuiWithSeq AS (SELECT  *, ROW_NUMBER() OVER (PARTITION BY LiuShui.代码 ORDER BY LiuShui.收款日期) AS SEQ FROM LiuShui),
     Resut AS (SELECT   LiuShuiWithSeq.代码,
                        LiuShuiWithSeq.名称,
                        LiuShuiWithSeq.收款日期 时间,
                        LiuShuiWithSeq.期初金额,
                        LiuShuiWithSeq.收款金额,
                        LiuShuiWithSeq.支出金额,
                        CAST( LiuShuiWithSeq.即时余额 + LiuShuiWithSeq.收款金额 - LiuShuiWithSeq.支出金额 AS DECIMAL(18,4)) 即时余额,
                        LiuShuiWithSeq.SEQ
               FROM     LiuShuiWithSeq
               WHERE    SEQ = 1
               UNION ALL
               SELECT   A.代码, A.名称, A.收款日期 时间, B.即时余额, A.收款金额, A.支出金额, CAST(B.即时余额 + A.收款金额 - A.支出金额 AS DECIMAL(18,4)) 即时余额, A.SEQ
               FROM     LiuShuiWithSeq A
                        INNER JOIN Resut B ON A.代码 = B.代码 AND   A.SEQ = B.SEQ + 1)
SELECT  * FROM  Resut ORDER BY  Resut.代码, Resut.时间;

drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
低代码开发平台 2021-03-04
  • 打赏
  • 举报
 回复
引用 3 楼 Hello World, 的回复:
先组成流水，再计算出各项的结余： 

CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DATETIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DATETIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) ;
WITH LiuShui AS (SELECT A.*, B.收款日期, B.收款金额, CAST(0 AS INT) AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzShoukuanBill B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, B.转账金额, CAST(0 AS INT)
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转入银行账户ID
                 UNION ALL
                 SELECT A.*, B.收款日期, CAST(0 AS INT) 收款金额, B.收款金额 AS 支出金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzFukuan B ON A.ID = B.银行账户ID
                 UNION ALL
                 SELECT A.*, B.转账日期, CAST(0 AS INT), B.转账金额
                 FROM   #jzBancAcount A
                        INNER JOIN #jzZhuanzhang B ON A.ID = B.转出银行账户ID),
     LiuShuiWithSeq AS (SELECT  *, ROW_NUMBER() OVER (PARTITION BY LiuShui.代码 ORDER BY LiuShui.收款日期) AS SEQ FROM LiuShui),
     Resut AS (SELECT   LiuShuiWithSeq.代码,
                        LiuShuiWithSeq.名称,
                        LiuShuiWithSeq.收款日期 时间,
                        LiuShuiWithSeq.期初金额,
                        LiuShuiWithSeq.收款金额,
                        LiuShuiWithSeq.支出金额,
                        LiuShuiWithSeq.即时余额 + LiuShuiWithSeq.收款金额 - LiuShuiWithSeq.支出金额 即时余额,
                        LiuShuiWithSeq.SEQ
               FROM     LiuShuiWithSeq
               WHERE    SEQ = 1
               UNION ALL
               SELECT   A.代码, A.名称, A.收款日期 时间, B.即时余额, A.收款金额, A.支出金额, B.即时余额 + A.收款金额 - A.支出金额 即时余额, A.SEQ
               FROM     LiuShuiWithSeq A
                        INNER JOIN Resut B ON A.代码 = B.代码 AND   A.SEQ = B.SEQ + 1)
SELECT  * FROM  Resut ORDER BY  Resut.代码, Resut.时间;

drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
这里换个数据类型就报错了！
低代码开发平台 2021-03-04
  • 打赏
  • 举报
 回复
CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20) ,开户日期 Date,期初金额 decimal,即时余额 decimal) INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000) INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000) CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DATETIME,收款金额 decimal) INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500) INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300) INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100) INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250) INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350) CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DATETIME,收款金额 decimal) INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50) INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30) INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10) INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250) INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DATETIME,转账金额 decimal) INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250) INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) ; WITH LiuShui AS (SELECT A.*, B.收款日期, B.收款金额, CAST(0 AS decimal) AS 支出金额 FROM #jzBancAcount A INNER JOIN #jzShoukuanBill B ON A.ID = B.银行账户ID UNION ALL SELECT A.*, B.转账日期, B.转账金额, CAST(0 AS decimal) FROM #jzBancAcount A INNER JOIN #jzZhuanzhang B ON A.ID = B.转入银行账户ID UNION ALL SELECT A.*, B.收款日期, CAST(0 AS decimal) 收款金额, B.收款金额 AS 支出金额 FROM #jzBancAcount A INNER JOIN #jzFukuan B ON A.ID = B.银行账户ID UNION ALL SELECT A.*, B.转账日期, CAST(0 AS decimal), B.转账金额 FROM #jzBancAcount A INNER JOIN #jzZhuanzhang B ON A.ID = B.转出银行账户ID), LiuShuiWithSeq AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY LiuShui.代码 ORDER BY LiuShui.收款日期) AS SEQ FROM LiuShui), Resut AS (SELECT LiuShuiWithSeq.代码, LiuShuiWithSeq.名称, LiuShuiWithSeq.收款日期 时间, LiuShuiWithSeq.期初金额, LiuShuiWithSeq.收款金额, LiuShuiWithSeq.支出金额, LiuShuiWithSeq.即时余额 + LiuShuiWithSeq.收款金额 - LiuShuiWithSeq.支出金额 即时余额, LiuShuiWithSeq.SEQ FROM LiuShuiWithSeq WHERE SEQ = 1 UNION ALL SELECT A.代码, A.名称, A.收款日期 时间, B.即时余额, A.收款金额, A.支出金额, B.即时余额 + A.收款金额 - A.支出金额 即时余额, A.SEQ FROM LiuShuiWithSeq A INNER JOIN Resut B ON A.代码 = B.代码 AND A.SEQ = B.SEQ + 1) SELECT * FROM Resut ORDER BY Resut.代码, Resut.时间; drop table #jzBancAcount drop table #jzShoukuanBill drop table #jzFukuan drop table #jzZhuanzhang 换个数据类型就报错了，因为金额一般不是整数型
RINK_1 2021-03-04
  • 打赏
  • 举报
 回复 


CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20)  ,开户日期 Date,期初金额 int,即时余额 int)
INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000)
INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000)
 
CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500)
INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300)
INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100)
 

INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250)
INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350)
 
CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 DateTIME,收款金额  int)
INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50)
INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30)
INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10)
 

INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250)
INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150)

 
 
CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 DateTIME,转账金额  int)
INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250)
INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150)

WITH CTE
AS
(SELECT 银行账户ID,收款日期 AS 日期,收款金额,0 AS 付款金额,1 AS RP_FLAG FROM #jzShoukuanBill
UNION ALL
SELECT 银行账户ID,收款日期 AS 日期,0,收款金额,-1 AS RP_FLAG FROM #jzFukuan
UNION ALL
SELECT 转入银行账户ID,转账日期 AS 日期,转账金额,0,1 AS RP_FLAG FROM #jzZhuanzhang
UNION ALL
SELECT 转出银行账户ID,转账日期 AS 日期,0,转账金额,-1 AS RP_FLAG FROM #jzZhuanzhang)


SELECT ROW_NUMBER() OVER (PARTITION BY 银行账户ID ORDER BY 日期) AS SEQ,
A.日期,B.代码,B.名称,ISNULL(C.SUB_TOTAL+期初金额,期初金额) AS 期初金额,A.收款金额,A.付款金额,
ISNULL(C.SUB_TOTAL+期初金额,期初金额)+RP_FLAG*(付款金额+收款金额) AS 即时余款
FROM CTE A
JOIN #jzBancAcount B ON A.银行账户ID=B.ID
CROSS APPLY (SELECT SUM(RP_FLAG*(付款金额+收款金额)) AS SUB_TOTAL FROM CTE WHERE A.银行账户ID=银行账户ID AND A.日期>日期) AS C
ORDER BY 银行账户ID,日期


drop table #jzBancAcount
drop table #jzShoukuanBill
drop table #jzFukuan
drop table #jzZhuanzhang
低代码开发平台 2021-03-03
  • 打赏
  • 举报
 回复
最后一个序号错了，14
低代码开发平台 2021-03-03
  • 打赏
  • 举报
 回复
/* 银行账户表jzBancAcount(主键ID,代码,名称,开户日期，期初金额,即时余额) 收款表：jzShoukuanBill(主键ID，收款日期，收款金额，银行账户ID) 付款表：jzFukuan(主键ID，付款日期，付款金额，银行账户ID) 转账表：jzZhuanzhang(主键ID,转账日期，转出银行账户，转入银行账户，转账金额) 求一个银行的交易流水按日期排序 结果如下： 交易序号（按流水增长从1开始），日期，账户代码，账户名称，之前金额 收款 付款 即时余额 */ CREATE TABLE #jzBancAcount(ID int,代码 VARCHAR(20),名称 VARCHAR(20) ,开户日期 Date,期初金额 int,即时余额 int) INSERT INTO #jzBancAcount values(1,'001','招商账户','2021-03-01',1000,1000) INSERT INTO #jzBancAcount values(2,'002','建行账户','2021-03-01',2000,2000) CREATE TABLE #jzShoukuanBill(ID int,银行账户ID int ,收款日期 Date,收款金额 int) INSERT INTO #jzShoukuanBill values(1,1,'2021-03-01 9:00:00',500) INSERT INTO #jzShoukuanBill values(2,1,'2021-03-01 10:00:00',300) INSERT INTO #jzShoukuanBill values(3,1,'2021-03-02 11:00:00',100) INSERT INTO #jzShoukuanBill values(5,2,'2021-03-01 9:00:00',250) INSERT INTO #jzShoukuanBill values(6,2,'2021-03-06 9:00:00',350) CREATE TABLE #jzFukuan(ID int,银行账户ID int ,收款日期 Date,收款金额 int) INSERT INTO #jzFukuan values(1,1,'2021-03-01 9:10:00',50) INSERT INTO #jzFukuan values(2,1,'2021-03-01 10:10:00',30) INSERT INTO #jzFukuan values(3,1,'2021-03-02 11:10:00',10) INSERT INTO #jzFukuan values(5,2,'2021-03-01 9:10:00',250) INSERT INTO #jzFukuan values(6,2,'2021-03-06 8:00:00',150) CREATE TABLE #jzZhuanzhang(ID int,转出银行账户ID int ,转入银行账户ID int ,转账日期 Date,转账金额 int) INSERT INTO #jzZhuanzhang values(1,2,1,'2021-03-01 9:30:00',250) INSERT INTO #jzZhuanzhang values(1,1,2,'2021-03-06 8:30:00',150) /*求一个sql语句得到如下结果*/ 序号 日期 账户代码 账户名称 期初金额 收款 付款 即时余款 1 2021-03-01 9:00:00 001 招商账户 1000 500 0 1500 2 2021-03-01 9:10:00 001 招商账户 1500 0 50 1450 3 2021-03-01 9:30:00 001 招商账户 1450 250 0 1700 4 2021-03-01 10:00:00 001 招商账户 1700 300 0 2000 5 2021-03-01 10:10:00 001 招商账户 2000 0 30 1970 6 2021-03-02 11:00:00 001 招商账户 1970 100 0 2070 7 2021-03-02 11:10:00 001 招商账户 2070 0 10 2060 8 2021-03-06 8:30:00 001 招商账户 2060 0 150 1910 9 2021-03-01 9:00:00 002 建行账户 2000 250 0 2250 10 2021-03-01 9:10:00 002 建行账户 2250 0 250 2000 11 2021-03-01 9:30:00 002 建行账户 2000 0 250 1750 12 2021-03-06 8:00:00 002 建行账户 1750 0 150 1600 13 2021-03-06 8:30:00 002 建行账户 1600 150 0 1750 13 2021-03-06 9:00:00 002 建行账户 1750 350 0 2100 drop table #jzBancAcount drop table #jzShoukuanBill drop table #jzFukuan drop table #jzZhuanzhang
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
sql语言的艺术
-----------------------Page1----------------------- SQL SSQQLL语言艺术 内容介绍 本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则进行解释说明。这些例 子大多来自于实际案例，对九种SQL经...
写给运营同学和初学者的SQL入门教程
作者简介 多肉，饿了么资深python工程师。曾在17年担任饿了么即时配送众包系统的研发经理，这篇文章最早的版本就诞生于那段时间，目前负责配送相关业务系统的整体稳定性建设。个人比较喜欢c和python，最近有点迷rust，同时还是个archlinux的日常用户，有类似爱好的欢迎交流 Preface Introduction 1 预备知识 1.1 数据库和数据表 1.2 最基本的Sql查询语...
【转】SQL语言艺术
SQL 语言艺术 内容介绍 本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则进行解释说明。这些例 子大多来自于实际案例，对九种SQL经典查询场景以及其性能影响讨论，非常便于实践，为你 的实际工作提出了具体建议。本书适合SQL数据库开发者、软件架构师，也适
2021爱分析･区域性银行数字化厂商全景报告
报告编委 报告指导人 张 扬 爱分析联合创始人&首席分析师 报告执笔人 孙文瑞 爱分析高级分析师 李 娜 爱分析分析师 戴 甜 爱分析分析师 目录 研究范围定义 全场景地图 场景定义与厂商解读 入选厂商列表 1. 研究范围定义 研究范围 随着金融监管的加强与金融规范的完善，以及受国有大行、股份制银行、互联网银行和外资银行的挤压，区域性银行面临着“不变则衰”的发展困局。 本次银行数字化厂商全景报告的研究范围为大中型区域性银行，即经营范围限定在某一区域（多为省、市、县、乡），资.
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章