MySQL JOIN查询帮助：对于最左侧表中的每一行，只返回最右侧表中的一行

I have two tables. I want to join them in a way that only one record in the right table is returned for each record in the left most table. I've included an example below. I'd like to avoid subqueries and temporary tables as the actual data is about 4M rows. I also don't care which record in the rightmost table is matched, as long as one or none is matched. Thanks!

我有两张桌子。我想以这样的方式加入它们：对于最左边的表中的每个记录，只返回右表中的一个记录。我在下面列举了一个例子。我想避免使用子查询和临时表，因为实际数据大约是4M行。我也不关心最右边的表中哪个记录匹配，只要匹配一个或没有。谢谢！

table users:

表用户：

-------------
| id | name |
-------------
| 1  | mike |
| 2  | john |
| 3  | bill |
-------------

table transactions:

表交易：

---------------
| uid | spent | 
---------------
| 1   | 5.00  |
| 1   | 5.00  |
| 2   | 5.00  |
| 3   | 5.00  |
| 3   | 10.00 |
---------------

expected output:

预期产量：

---------------------
| id | name | spent |
---------------------
| 1  | mike | 5.00  |
| 2  | john | 5.00  |
| 3  | bill | 5.00  |
---------------------

4 个解决方案

#1

Use:

使用：

  SELECT u.id,
         u.name,
         MIN(t.spent) AS spent
    FROM USERS u
    JOIN TRANSACTIONS t ON t.uid = u.id
GROUP BY u.id, u.name

Mind that this will only return users who have at least one TRANSACTIONS record. If you want to see users who don't have supporting records as well as those who do - use:

请注意，这只会返回至少有一个TRANSACTIONS记录的用户。如果您想查看没有支持记录的用户以及那些用户，请使用：

   SELECT u.id,
          u.name,
          COALESCE(MIN(t.spent), 0) AS spent
     FROM USERS u
LEFT JOIN TRANSACTIONS t ON t.uid = u.id
 GROUP BY u.id, u.name

#2

If you do not care about the particular row that you get back.

如果你不关心你回来的特定行。

select id, name, spent
from users
left join transactions on users.id = transactions.uid
group by id

This will return one row per user. It will be the first matched transaction.

这将为每个用户返回一行。这将是第一个匹配的交易。

#3

See SO 3305709 for an equivalent recent question, with a number of reasonable answers. The DBMS cited there is Postgres, but the differences here are negligible.

请参阅SO 3305709了解最近的等效问题，并提供了许多合理的答案。 DBMS引用了Postgres，但这里的差异可以忽略不计。

#4

My apologies if this doesn't actually answer your question. It looks like you are trying to see which users have at least one transaction. You could do this and in the process see how much each user has spent by doing something like this:

如果这实际上没有回答你的问题我很抱歉。看起来您正在尝试查看哪些用户至少有一个交易。您可以执行此操作，并在此过程中查看每个用户通过执行以下操作所花费的金额：

SELECT 
    u.id,
    u.name,
    SUM(t.spent) AS total
FROM 
    USERS u
    INNER JOIN TRANSACTIONS t 
        ON t.uid = u.id
GROUP BY 
    u.id
    , u.name

#1