连接表本身 - 性能
我想要一些有关以下加入的帮助。 我有一张表（大约有 2000 万行），其中包含：
MemberId（主键）| ID（主键）|交易日期 |余额
我想在一次查询中获取所有客户的最新余额。 我知道我可以做这样的事情（我只是凭记忆写的）。但这种方式实在是太慢了。
SELECT *
FROM money
WHERE money.Id = (SELECT MAX(Id)
FROM money AS m
WHERE m.MemberId = money.MemberId)
还有其他（更快/更智能）的选择吗？
I would like some help with the following join.
I have one table (with about 20 million rows) that consists of:
MemberId (Primary Key) | Id (Primary Key) | TransactionDate | Balance
I would like to get the latest Balance for all the customers in one query.
I know I could do something like this (I just wrote it from my memory). But this way is terribly slow.
SELECT *
FROM money
WHERE money.Id = (SELECT MAX(Id)
FROM money AS m
WHERE m.MemberId = money.MemberId)
Are there any other (faster/smarter) options?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在我经历过的所有优化教程和截屏视频中，连接总是比子查询更受青睐。使用子查询时，每次比较都会执行子查询，而连接只执行一次。
In all optimization tutorials and screencasts that I've endured through, joins are always favoured over subqueries. When using a sub-query the sub-query is executed for each comparison, where as with a join only once.
加入并不是解决此问题的最佳方法。考虑使用 GROUP BY 子句筛选每个成员的最后一笔交易，如下所示：
SELECT MemberId, MAX(Id), TransactionDate, Balance FROM Money GROUP BY MemberIdUPDATE
正如库尔德工人党指出的，平衡将是随机选择的。看来您毕竟必须执行某种连接。考虑这个选项：
JOINing is not the best way to go about this. Consider using a GROUP BY clause to sift out the last transaction for each member, like this:
SELECT MemberId, MAX(Id), TransactionDate, Balance FROM money GROUP BY MemberIdUPDATE
as PKK pointed out, balance will be chosen randomly. It looks like you'll have to perform some sort of join after all. Consider this option:
另一种选择是在左连接中查找 NULL 值：
但是当然 Umbrella 的答案更好。
Other option is to lookup for NULL values in a left join:
But of course Umbrella's answer is better.