我正在使用SQL,我有一个包含三个列的表:account,transaction_date,Points。每个帐户都有多个transaction_dates和每笔交易获得的积分。
当每个帐户达到某个阈值(即累积100点)时,如何返回transaction_date。假设第一个帐户有2000个交易,前五个帐户有21个交易点。我希望查询返回事务#5,因为这是帐户达到100时。
发布于 2019-04-18 08:39:28
这应该可以让你得到你想要的东西。
SELECT account_id, MIN(transaction_date) FROM table t1 WHERE (SELECT SUM(points) FROM table t2 WHERE t2.transaction_date < t1.transaction_date) <= 100 GROUP BY account_id
发布于 2019-04-18 09:41:22
select min(a.transaction_date), a.account from
(select sum(t1.points) as thesum, t2.transaction_date, t2.account
from table t1
inner join table t2 on t1.account = t2.account and t1.transaction_date <= t2.transaction_date
group by t2.transaction_date, t2.account
having thesum >= 100) a
group by a.account
https://stackoverflow.com/questions/-100001126
复制相似问题