时间戳

首页 > 解决方案 > 用于连接两个表并仅显示具有最新时间戳的结果的 SQL 脚本？

sql - 用于连接两个表并仅显示具有最新时间戳的结果的 SQL 脚本？

问题描述

假设我是一家银行：

我有一张表，其中包含一组名为 的用户身份user_identity，还有user_balance一张表，其中列出了用户曾经拥有的所有余额。

user_balance每次用户从他们的账户存款或取款时，都会创建一个新条目。每个条目都有以下字段：

  1. timestamp- 记录余额的日期
  2. id- 唯一标识符
  3. userId- 拥有该帐户的用户的 ID
  4. amountCents- 余额的美分金额
  5. relatedTransferId- 导致创建新余额的取款或存款的唯一标识符
  6. previousBalanceId- 之前余额的唯一标识符

我想写两个脚本：

  1. 一种返回当前存储在所有帐户中的所有资金的总和
  2. 一个返回所有用户及其当前余额的列表

这是我写第二个的尝试：

SELECT 
    user_balance."amountCents" * (1/100) AS balance, 
    "user"."identityId",
    user_identity."firstName",
    user_identity."lastName",
    user_balance.timestamp
FROM user_balance
JOIN user_identity
    ON user_identity.id = user_balance."userId"
ORDER BY 1;

我不知道如何只为给定用户选择最近的余额。任何和所有的帮助将不胜感激！

user_balance示例表（请注意每个用户 ID 可能有多个条目，因为每次余额更改时都有一个条目！）：

            timestamp           |     userId      | amountCents
--------------------------------+-----------------+--------------
  2021-05-12 07:02:05.088+00:00 | u_2przr9gcn52sm |       20607
  2021-05-12 17:08:05.552+00:00 | u_ysvzqzs7f862  |           0
  2021-05-19 00:40:29.62+00:00  | u_y9eyf0j3b5i5  |      407454
  2021-05-16 21:47:34.123+00:00 | u_jze8wwmuuudk  |       66500
  2021-05-14 08:01:03.929+00:00 | u_22cyb1sctel5k |     3881602
  2021-05-17 05:01:02.711+00:00 | u_22cyb1sctel5k |     3435703

user_identity样品表：

         id        |         firstName          |      lastName
-------------------+----------------------------+----------------------
  ui_104xy9vczsrmy | John                       | Doe
  ui_10holeidg2ydt | John                       | Doe
  ui_10no0d6mtvdfi | John                       | Doe

期望的结果：

      firstName    |         lastName           |      currentBalance
-------------------+----------------------------+----------------------
  John             | Doe                        | 3000
  John             | Doe                        | 2000
  John             | Doe                        | 1000

（currentBalance最终表中的余额是与表中最近的时间戳相关联的user_balance）。

标签： sqljoinleft-joincockroachdb

解决方案

您可以使用ROW_NUMBER()表表达式来识别您想要的行并排除其他行。

例如：

select
  i.firstName,
  i.lastName,
  x.amountCents as currentBalance
from user_identity i
left join (
  select b.*,
    row_number() over(partition by b.userId 
                      order by b.timestamp desc) as rn
  from user_balance b
) x on x.id = i.userId
where x.rn = 1

推荐阅读