mysql - 在Mysql中获取特定日期之间的数据

Question

我有三个表，分别名为User、Role 和Balance_updates。 User 表保存有关用户的信息，Role 描述用户类型，如 Customer、Admin、Manager 和 Balance_updates 存储有关余额的所有交易，即它存储历史记录关于与余额相关的交易。

表格

用户

+-----------------------+--------------+------+-----+-------------------+-------+
| Field                 | Type         | Null | Key | Default           | Extra |
+-----------------------+--------------+------+-----+-------------------+-------+
| username              | varchar(20)  | NO   | PRI | NULL              |       |
| password              | varchar(32)  | NO   |     | NULL              |       |
| email                 | varchar(50)  | YES  |     | NULL              |       |
| role_id               | int(11)      | NO   | MUL | NULL              |       |
| mobile_wallet_balance | double(20,2) | NO   |     | 0.00              |       |
| merit_point           | bigint(20)   | YES  |     | NULL              |       |
| status                | int(11)      | NO   |     | NULL              |       |
| is_auto_btm_enabled   | tinyint(1)   | YES  |     | 0                 |       |
| created_at            | datetime     | YES  |     | NULL              |       |
| updated_at            | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
| gender                | varchar(20)  | YES  |     | NULL              |       |
| validity              | date         | YES  |     | NULL              |       |
| status_desc           | text         | YES  |     | NULL              |       |
+-----------------------+--------------+------+-----+-------------------+-------+

Role

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(25)  | NO   | UNI | NULL    |                |
| description | varchar(255) | YES  |     | NULL    |                |
| value       | varchar(25)  | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

余额更新

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| username   | varchar(15)  | NO   |     | NULL              |                |
| role_id    | int(11)      | YES  |     | NULL              |                |
| amount     | double(20,2) | YES  |     | NULL              |                |
| updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

Balance_updates 中的数据

目标

我想在任何给定日期生成所有用户及其各自的余额；例如，如果我想要给定日期 2012-12-28 的余额报表，它应该从 Balance_updates 生成最新余额。

我尝试过的

SELECT DISTINCT (
u.username
), r.value, u.amount AS `amount`
FROM Balance_updates u
INNER JOIN Role r ON u.role_id = r.id
WHERE u.amount > 0.0 && UNIX_TIMESTAMP( u.updated_at ) < UNIX_TIMESTAMP( '2013-1-3 23:59:59' )
ORDER BY r.value, UNIX_TIMESTAMP( u.updated_at ) DESC

结果和问题

如您所料，它返回 Balance_updates 的所有值，即记录的所有其他用户交易。

问题:

1.如何获取用户在特定日期的最新余额报表。如果在该日期用户余额未更改，则显示上次更改前的余额。

任何帮助将不胜感激；

Answer 1

未测试，但我感觉这可行(并且可以优化)。

SELECT DISTINCT u.username, r.value, u.amount AS `amount`
FROM Balance_updates u
INNER JOIN Role r ON u.role_id = r.id
WHERE u.updated_at = (
    SELECT MAX(inner_u.updated_at)
    FROM Balance_updates AS inner_u
    WHERE
        inner_u.username = u.username
        && amount > 0
        && UNIX_TIMESTAMP( inner_u.updated_at ) < UNIX_TIMESTAMP( '2013-1-3 23:59:59' )
)
ORDER BY r.value, UNIX_TIMESTAMP( u.updated_at ) DESC

只是一个题外话的建议:

• 添加 surrogate key到表 User(例如 User.id)；使其成为主键
• 将 Balance_updates.username 列替换为(例如)Balance_updates.user_id，并存储对 User.id 的引用(foreign key ).

或

• 将 Balance_updates.username 列更改为 VARCHAR(20) 以匹配 User.username 的类型