如何对每个帐户的最后一笔交易进行SQL查询? [英] How to make a SQL query for last transaction of every account?

问题描述

说我有一个表"transactions"，其中有"acct_id"，"trans_date"和"trans_type"列，我想过滤该表，以便每个账户只有最后一笔交易.显然我可以做类似的事情

Say I have a table "transactions" that has columns "acct_id" "trans_date" and "trans_type" and I want to filter this table so that I have just the last transaction for each account. Clearly I could do something like

SELECT acct_id, max(trans_date) as trans_date  
FROM transactions GROUP BY acct_id;

但是我失去了我的trans_type.然后，我可以使用日期和帐户ID列表进行第二次SQL调用，并获取我的trans_type，但是这很笨拙，因为这意味着要么将数据来回发送到sql服务器，要么意味着创建一个临时表.

but then I lose my trans_type. I could then do a second SQL call with my list of dates and account id's and get my trans_type back but that feels very cludgy since it means either sending data back and forth to the sql server or it means creating a temporary table.

有没有一种方法可以通过单个查询来完成此操作，希望这是一种可以与mysql，postgres，sql-server和oracle一起使用的通用方法.

Is there a way to do this with a single query, hopefully a generic method that would work with mysql, postgres, sql-server, and oracle.

推荐答案

这是 greestst的示例-n-per-group 查询.这个问题每周都会在StackOverflow上出现几次.除了其他人提供的子查询解决方案之外，这也是我的首选解决方案，该解决方案不使用子查询，GROUP BY或CTE:

This is an example of a greatest-n-per-group query. This question comes up several times per week on StackOverflow. In addition to the subquery solutions given by other folks, here's my preferred solution, which uses no subquery, GROUP BY, or CTE:

SELECT t1.*
FROM transactions t1
LEFT OUTER JOIN transactions t2
  ON (t1.acct_id = t2.acct_id AND t1.trans_date < t2.trans_date)
WHERE t2.acct_id IS NULL;

换句话说，返回一行，以使没有其他行具有相同的acct_id和更大的trans_date.

In other words, return a row such that no other row exists with the same acct_id and a greater trans_date.

此解决方案假定trans_date对于给定帐户是唯一的，否则可能会发生联系，并且查询将返回所有绑定的行.但这也适用于其他人提供的所有解决方案.

This solution assumes that trans_date is unique for a given account, otherwise ties may occur and the query will return all tied rows. But this is true for all the solutions given by other folks too.

我更喜欢这种解决方案，因为我通常在MySQL上工作，而MySQL对GROUP BY的优化不是很好.因此，通常证明这种外部联接解决方案在性能上更好.

I prefer this solution because I most often work on MySQL, which doesn't optimize GROUP BY very well. So this outer join solution usually proves to be better for performance.

这篇关于如何对每个帐户的最后一笔交易进行SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！