mysql - 跨连接表求和

Question

考虑以下场景:一位用户拥有一个或多个(银行)账户。帐户余额的历史变化在表格中进行跟踪。我想及时显示用户所有账户的资金历史。这些是表定义:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `accounts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);


CREATE TABLE `balances` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `created_at` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

这里是一些示例数据:

INSERT INTO `users` (`id`, `name`)
VALUES
  (1,'Bob');

INSERT INTO `accounts` (`id`, `user_id`)
VALUES
  (1,1),
  (2,1);

INSERT INTO `balances` (`id`, `account_id`, `amount`, `created_at`)
VALUES
  (1,1,100, '2012-01-01'),
  (2,1,150, '2012-01-02'),
  (3,2,1000,'2012-01-04'),
  (4,2,1100,'2012-01-08'),
  (5,1,175, '2012-01-10');

一个用户每天只能跨账户进行一次存款/取款，因此 created_at 值可以被认为是唯一的。

鉴于示例数据，我想编写的查询结果应该是这样的:

|'2012-01-01'|100 |
|'2012-01-02'|150 |
|'2012-01-04'|1150|
|'2012-01-08'|1250|
|'2012-01-10'|1275|

计划如下:

1. 从余额表中获取所有唯一日期。
2. 对于每个日期，选择每个帐户中的最新余额，以便余额的日期不超过步骤 1 中的日期。
3. 对第 2 步中找到的金额求和，忽略 NULL 值。 NULL 值表示该帐户的第一个记录余额是较晚的日期。

我在制定第 2 步的条件时遇到问题。

MySQL Fiddle

Answer 1

select user_id,created_at,sum(accAmount) from
(
select a.id account_id,a.user_id user_id, dt.created_at created_at,
( select amount from balances b3 where (b3.account_id=a.id)
and b3.created_at=
(select max(created_at) as lastAccDate from balances b
where b.created_at<=dt.created_at and b.account_id=a.id)
) AccAmount
from accounts a,
(select distinct created_at from balances) dt
) AccountAmounts
group by user_id,created_at
order by 2