mysql - sql查询获取具有特定时间戳的所有用户的事务

Question

我的sql表结构如下:

id INT(11),
amount DOUBLE(10,2),
createdAt DATETIME,
user_id INT(11),
tx_status INT(11).

tx_status 的值可以是 1(贷方)、2(借方)和 3(已结算)。现在我想要的是，当特定用户的 tx_status = 3 时，我想要所有用户的所有最新交易，其中交易 createAt 大于上次创建的交易。

数据如下:

id .  amount . createdAt .          user_id .      tx_status
112   500 .    2019-05-15 00:30:40  345 .          1
113 . 300      2019-05-16 06:45:29  345 .          2
116 . 600 .    2019-05-16 06:45:33  345 .          3
119 . 567 .    2019-05-17 07:56:55  345 .          1
121   500 .    2019-05-18 00:30:40  346 .          1
123 . 300      2019-05-19 06:45:29  346 .          2
124 . 600 .    2019-05-20 06:45:33  346 .          3
126 . 567 .    2019-05-21 07:56:55  346 .          1

输出应该是:

id .  amount . createdAt .          user_id .      tx_status
119 . 567 .    2019-05-17 07:56:55  345 .          1
126 . 567 .    2019-05-21 07:56:55  346 .          1

Answer 1

此查询将为您提供您想要的结果。它使用结算发生的最大时间的派生表，使用条件聚合来确保如果没有发生结算，则该时间为“1970-01-01”(以便返回所有记录)。

SELECT t1.*
FROM transactions t1
JOIN (SELECT user_id, MAX(CASE WHEN tx_status = 3 THEN createdAt ELSE '1970-01-01' END) AS last_settlement
      FROM transactions t2
      GROUP BY user_id) t2 ON t2.user_id = t1.user_id AND t2.last_settlement < t1.createdAt

输出(用于您的示例数据)

id  amount  createdAt           user_id tx_status
119 567     2019-05-17 07:56:55 345     1   
126 567     2019-05-21 07:56:55 346     1

Demo on dbfiddle