sql - 在sqlite中制作分类帐？

Question

我有 table

t1(插入客户数据)

  Date       id    idCl nameCl   opening   
1-8-2016     10L   CL-J  Jon        0     
15-8-2016    20L   CL-B  Ben        0   
25-8-2016    15.5L CL-A  Alina      0     
28-8-2016    10L   CL-B  Ben        0
30-8-2016    20L   CL-J  Jon        0

t2(客户收到的付款)

Date       id    idCl nameCl   receive  
1-9-2016     10L   CL-J  Jon      2000 
1-9-2016     10L   CL-J  Jon      1000   
5-9-2016     20L   CL-B  Ben      3000  
10-9-2016    15.5L CL-A  Alina    5000  
12-9-2016    10L   CL-B  Ben      8000  
22-9-2016    20L   CL-J  Jon      2000  

我想要这样的t3

Date       id   idCl nameCl    opening   dr     closing
1-9-2016     10L   CL-J  Jon        0      2000     2000
22-9-2016    20L   CL-J  Jon       2000    1000     3000
1-9-2016     10L   CL-J  Jon       3000    2000     5000  
5-9-2016     20L   CL-B  Ben        0      3000     3000
12-9-2016    10L   CL-B  Ben       3000    8000    11000
10-9-2016    15.5L CL-A  Alina      0      5000     5000

主要部分是如何在每笔交易中使客户的期末余额变为期初余额。

Answer 1

这是一个两部分的解决方案。
第一部分(在最初的 mcve-makig 之后)是一个最小查询，以接近您想要的输出(除了 9 月 22 日的奇怪排序；10 日之前的 12 日是因为 idCl 具有更高优先级的排序)。
第二部分花费了相当多的精力来按日期排序，并相应地格式化。

-- make my mcve
-- (including unused table t1, as a nicety to others wanting to answer)
drop table if exists t1 ;
create table t1 (Date date, id varchar(10), idCl varchar(10), nameCl varchar(30), opening int);
drop table if exists t2;
create table t2 (Date date, id varchar(10), idCl varchar(10), nameCl varchar(30), receive int);

insert into t1 values ('1-8-2016',  '10L',   'CL-J',  'Jon', 0); 
insert into t1 values ('15-8-2016', '20L',   'CL-B',  'Ben', 0); 
insert into t1 values ('25-8-2016', '15.5L', 'CL-A',  'Alina', 0); 
insert into t1 values ('28-8-2016', '10L',   'CL-B',  'Ben', 0); 
insert into t1 values ('30-8-2016', '20L',   'CL-J',  'Jon', 0); 

insert into t2 values ('1-9-2016',  '10L',   'CL-J',  'Jon',   2000); 
insert into t2 values ('1-9-2016',  '10L',   'CL-J',  'Jon',   1000); 
insert into t2 values ('5-9-2016',  '20L',   'CL-B',  'Ben',   3000); 
insert into t2 values ('10-9-2016', '15.5L', 'CL-A',  'Alina', 5000); 
insert into t2 values ('12-9-2016', '10L',   'CL-B',  'Ben',   8000); 
insert into t2 values ('22-9-2016', '20L',   'CL-J',  'Jon',   2000); 


.headers off
select '-- simple query, not sorting by date';
.headers on
-- making pretty
.mode column
select 
    a.date,
    a.id, a.idCl, a.nameCL,
    sum(b.receive)-a.receive as opening,
    a.receive               as dr,
    sum(b.receive)          as closing
from t2 as a inner join t2 as b using (idCl)
where    a.rowid >= b.rowid
group by a.rowid 
order by a.idCl DESC
;

-- preparing the use of date for sorting
drop table if exists moneys;
create table moneys (Date date, id varchar(10), idCl varchar(10), nameCl varchar(30), amount int);
insert into moneys select 
    date(       substr(date,-4,4)
         ||'-'||substr('0'||replace(substr(date, instr(date, '-'), 3),'-', ''),-2,2)
         ||'-'||substr('0'||replace(substr(date, 1,2),'-',''),-2,2)
    ),
    id,
    idCl,
    nameCl,
    receive
from t2;

.headers off
select '';
select '-- advanced query, sorting by correct date';
.headers on
select 
    a.date, a.id, a.idCl, a.nameCL,
    sum(b.amount)-a.amount as opening,
    a.amount               as dr,
    sum(b.amount)          as closing
from moneys as a inner join moneys as b using (idCl)
where    a.rowid >= b.rowid
group by a.rowid
order by a.idCl DESC , a.date
;

输出:

-- simple query, not sorting by date
Date        id          idCl        nameCl      opening     dr          closing
----------  ----------  ----------  ----------  ----------  ----------  ----------
1-9-2016    10L         CL-J        Jon         0           2000        2000
1-9-2016    10L         CL-J        Jon         2000        1000        3000
22-9-2016   20L         CL-J        Jon         3000        2000        5000
5-9-2016    20L         CL-B        Ben         0           3000        3000
12-9-2016   10L         CL-B        Ben         3000        8000        11000
10-9-2016   15.5L       CL-A        Alina       0           5000        5000

-- advanced query, sorting by correct date
Date        id          idCl        nameCl      opening     dr          closing
----------  ----------  ----------  ----------  ----------  ----------  ----------
2016-09-01  10L         CL-J        Jon         0           2000        2000
2016-09-01  10L         CL-J        Jon         2000        1000        3000
2016-09-22  20L         CL-J        Jon         3000        2000        5000
2016-09-05  20L         CL-B        Ben         0           3000        3000
2016-09-12  10L         CL-B        Ben         3000        8000        11000
2016-09-10  15.5L       CL-A        Alina       0           5000        5000

笔记:
1) 第二个查询顺序按时间顺序为输出本身和行排序以产生开场总和。
2) 不使用整个表 t1。例如，如果您只想处理与现有帐户匹配的条目，则在插入中间表“moneys”或要使用的 t2 表条目之前进行过滤。
3)“id”(与“idCL”相反)没有明显的影响，与您想要的输出相匹配，例如 Ben 总计为 11000。也许您想要所有“id”的总和。 (一开始我是分开处理id的，以为是不同的账户，因为是在t1分开开的。)