对于最左侧表中的每一行，仅返回最右侧表中的一行

2021-12-17 00:00:00 join sql mysql

我有两张桌子.我想以一种方式加入它们，即对于最左侧表中的每条记录只返回右侧表中的一条记录.我在下面包含了一个例子.我想避免使用子查询和临时表，因为实际数据大约有 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

相关文章