MySQL - 加入 2 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10230438/
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
MySQL - Join 2 tables
提问by SteveJDB
I have 2 tables: users & balance.
我有 2 个表：用户和余额。
I want to join the tables with all of the details from the user table (all fields of all tuples) with the most recent entry from the balance table (1 field linked by a user id).
我想将包含来自用户表（所有元组的所有字段）的所有详细信息的表与余额表中的最新条目（由用户 ID 链接的 1 个字段）连接起来。
Here is the structure of the tables:
以下是表的结构：
balance:
平衡：
+---------+
| Field |
+---------+
| dbid |
| userId |
| date |
| balance |
+---------+
users:
用户：
+-------------+
| Field |
+-------------+
| dbid |
| id |
| fName |
| sName |
| schedName |
| flexiLeave |
| clockStatus |
+-------------+
I have been trying for hours to do this and the closest I can get is to return a row for a single user:
我已经尝试了几个小时来做到这一点，我能得到的最接近的是为单个用户返回一行：
SELECT u.*, b.balance, b.date FROM users u, balance b WHERE u.id = b.userId AND b.date = (SELECT MAX(date) FROM balance WHERE userId = 'A8126982');
Or I can select all users but not the most recent entry in the balance table:
或者我可以选择所有用户但不选择余额表中的最新条目：
SELECT u.*, b.balance, b.date FROM users u, balance b WHERE u.id = b.userId GROUP BY u.id;
I have tried many different queries and seem to be getting closer but I just can't get to where I want to be.
我尝试了许多不同的查询，似乎越来越近了，但我就是无法到达我想去的地方。
Any help would be appreciated.
任何帮助，将不胜感激。
回答by Aleks G
You can use the first SQL you wrote but for all users:
您可以使用您编写的第一个 SQL，但适用于所有用户：
SELECT u.*, b.balance, b.date
FROM users u JOIN balance b ON u.id = b.userId
WHERE b.date = (SELECT MAX(date) FROM balance WHERE userId = u.id);
This may not be the fastest way to get the result, but it'll give you what you need. I use similar queries in quite a few places in my app.
这可能不是获得结果的最快方法，但它会为您提供所需的东西。我在应用程序的很多地方都使用了类似的查询。
