MySQL - 加入 2 个表

2021-12-17 00:00:00 join max select mysql

我有 2 个表:users &平衡.

I have 2 tables: users & balance.

我想将包含来自用户表(所有元组的所有字段)的所有详细信息的表与余额表中的最新条目(由用户 ID 链接的 1 个字段)连接起来.

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).

表的结构如下:

余额:

+---------+
| Field   |
+---------+
| dbid    |
| userId  |
| date    |
| balance |
+---------+

用户:

+-------------+
| 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.

任何帮助将不胜感激.

推荐答案

您可以使用您编写的第一个 SQL，但适用于所有用户:

You can use the first SQL you wrote but for all users:

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.

相关文章