我有桌子
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
主要部分是如何使每笔交易的期末余额变为客户的期初余额。
最佳答案
这是一个分为两部分的解决方案。
第一部分（在初始mcve-makig之后）是一个最小查询，可以接近所需的输出（9月22日的奇怪排序； 10月12日之前的12th是因为按优先级更高的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中分开开设的。）
关于sql - 使账本在sqlite？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39512929/