MySQL-联接2个表 [英] MySQL - Join 2 tables

问题描述

我有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.

这篇关于MySQL-联接2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！