如何对每个帐户的最后一笔交易进行 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1505549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a SQL query for last transaction of every account?
提问by Steven Noble
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
假设我有一个表“transactions”，其中包含“acct_id”、“trans_date”和“trans_type”列，我想过滤这个表，以便我只有每个帐户的最后一笔交易。显然我可以做类似的事情
SELECT acct_id, max(trans_date) as trans_date
FROM transactions GROUP BY acct_id;
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.
但后来我失去了我的 trans_type。然后，我可以使用我的日期和帐户 ID 列表进行第二次 SQL 调用，并取回我的 trans_type，但这感觉非常笨拙，因为这意味着要么向 sql 服务器来回发送数据，要么意味着创建一个临时表。
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.
有没有办法通过单个查询来做到这一点，希望是一种适用于 mysql、postgres、sql-server 和 oracle 的通用方法。
回答by Bill Karwin
This is an example of a greatest-n-per-groupquery. 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:
这是每组最大 n 次查询的示例。这个问题每周都会在 StackOverflow 上出现几次。除了其他人给出的子查询解决方案之外，这里是我的首选解决方案，它不使用子查询GROUP BY、 或 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;
In other words, return a row such that no other row exists with the same acct_idand a greater trans_date.
换句话说，返回一行使得不存在具有相同acct_id和更大的其他行trans_date。
This solution assumes that trans_dateis 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.
此解决方案假定trans_date对于给定帐户是唯一的，否则可能会出现绑定并且查询将返回所有绑定的行。但这也适用于其他人给出的所有解决方案。
I prefer this solution because I most often work on MySQL, which doesn't optimize GROUP BYvery well. So this outer join solution usually proves to be better for performance.
我更喜欢这个解决方案，因为我最经常在 MySQL 上工作，它优化GROUP BY得不是很好。所以这个外连接解决方案通常被证明对性能更好。
回答by David
This works on SQL Server...
这适用于 SQL Server ...
SELECT acct_id, trans_date, trans_type
FROM transactions a
WHERE trans_date = (
SELECT MAX( trans_date )
FROM transactions b
WHERE a.acct_id = b.acct_id
)
回答by Raj More
Try this
尝试这个
WITH
LastTransaction AS
(
SELECT acct_id, max(trans_date) as trans_date
FROM transactions
GROUP BY acct_id
),
AllTransactions AS
(
SELECT acct_id, trans_date, trans_type
FROM transactions
)
SELECT *
FROM AllTransactions
INNER JOIN LastTransaction
ON AllTransactions.acct_id = LastTransaction.acct_id
AND AllTransactions.trans_date = LastTransaction.trans_date
回答by RedFilter
select t.acct_id, t.trans_type, tm.trans_date
from transactions t
inner join (
SELECT acct_id, max(trans_date) as trans_date
FROM transactions
GROUP BY acct_id;
) tm on t.acct_id = tm.acct_id and t.trans_date = tm.trans_date
