MySQL 对于最左侧表中的每一行，仅返回最右侧表中的一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3313614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return only one row from the right-most table for every row in the left-most table
提问by Mike Sherov
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 |
---------------------
回答by OMG Ponies
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
回答by TheJacobTaylor
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.
这将为每个用户返回一行。这将是第一个匹配的交易。
回答by wshato
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
