MySQL JOIN查询帮助:对于最左边的表中的每一行，仅从最右边的表返回一行

问题描述

我有两个桌子.我想以一种方式将它们加入，即最左侧表中的每个记录只返回右侧表中的一个记录.我在下面提供了一个示例.我想避免使用子查询和临时表，因为实际数据约为4M行.我也不在乎最右边表中的哪条记录被匹配，只要一个或一个都不匹配即可.谢谢！

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!

表用户:

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

表交易:

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

预期输出:

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

推荐答案

使用:

  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

请注意，这只会返回至少具有一个TRANSACTIONS记录的用户.如果要查看没有支持记录的用户以及有支持记录的用户，请使用:

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:

   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

这篇关于MySQL JOIN查询帮助:对于最左边的表中的每一行，仅从最右边的表返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！