MySQL INNER JOIN 从第二个表中只选择一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12526194/
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
MySQL INNER JOIN select only one row from second table
提问by Wasim
I have a userstable and a paymentstable, for each user, those of which have payments, may have multiple associated payments in the paymentstable. I would like to select all users who have payments, but only select their latest payment. I'm trying this SQL but i've never tried nested SQL statements before so I want to know what i'm doing wrong. Appreciate the help
我有一个users表和一个payments表，对于每个用户，其中有付款的用户，表中可能有多个关联的付款payments。我想选择所有有付款的用户，但只选择他们最近的付款。我正在尝试这个 SQL，但我以前从未尝试过嵌套 SQL 语句，所以我想知道我做错了什么。感谢帮助
SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*
FROM payments AS p
ORDER BY date DESC
LIMIT 1
)
ON p.user_id = u.id
WHERE u.package = 1
回答by John Woo
You need to have a subquery to get their latest date per user ID.
您需要有一个子查询来获取每个user ID.
SELECT a.*, c.*
FROM users a
INNER JOIN payments c
ON a.id = c.user_ID
INNER JOIN
(
SELECT user_ID, MAX(date) maxDate
FROM payments
GROUP BY user_ID
) b ON c.user_ID = b.user_ID AND
c.date = b.maxDate
WHERE a.package = 1
回答by Finesse
SELECT u.*, p.*
FROM users AS u
INNER JOIN payments AS p ON p.id = (
SELECT id
FROM payments AS p2
WHERE p2.user_id = u.id
ORDER BY date DESC
LIMIT 1
)
This solution is better than the accepted answerbecause it works correctly when there are some payments with the same user and date.
此解决方案比接受的答案更好，因为当有相同用户和日期的某些付款时，它可以正常工作。
回答by Mihai Matei
回答by valex
SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*,
@num := if(@id = user_id, @num + 1, 1) as row_number,
@id := user_id as tmp
FROM payments AS p,
(SELECT @num := 0) x,
(SELECT @id := 0) y
ORDER BY p.user_id ASC, date DESC)
ON (p.user_id = u.id) and (p.row_number=1)
WHERE u.package = 1
回答by lc.
There are two problems with your query:
您的查询有两个问题：
- Every table and subquery needs a name, so you have to name the subquery
INNER JOIN (SELECT ...) AS p ON .... - The subquery as you have it only returns one row period, but you actually want one row for each user. For that you need one query to get the max date and then self-join back to get the whole row.
- 每个表和子查询都需要一个名称，因此您必须为子查询命名
INNER JOIN (SELECT ...) AS p ON ...。 - 您拥有的子查询仅返回一行时间段，但您实际上希望每个 user一行。为此，您需要一个查询来获取最大日期，然后自行返回以获取整行。
Assuming there are no ties for payments.date, try:
假设 没有关系payments.date，请尝试：
SELECT u.*, p.*
FROM (
SELECT MAX(p.date) AS date, p.user_id
FROM payments AS p
GROUP BY p.user_id
) AS latestP
INNER JOIN users AS u ON latestP.user_id = u.id
INNER JOIN payments AS p ON p.user_id = u.id AND p.date = latestP.date
WHERE u.package = 1
回答by Justice Eziefule
SELECT U.*, V.* FROM users AS U
INNER JOIN (SELECT *
FROM payments
WHERE id IN (
SELECT MAX(id)
FROM payments
GROUP BY user_id
)) AS V ON U.id = V.user_id
This will get it working
这将使它工作
回答by Hassan Dad Khan
Matei Mihai given a simple and efficient solution but it will not work until put a MAX(date)in SELECT part so this query will become:
Matei Mihai 给出了一个简单有效的解决方案，但MAX(date)在 SELECT 部分放入一个之前它不会工作，所以这个查询将变成：
SELECT u.*, p.*, max(date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id
And order by will not make any difference in grouping but it can order the final result provided by group by. I tried it and it worked for me.
而 order by 不会对分组产生任何影响，但它可以对 group by 提供的最终结果进行排序。我试过了，它对我有用。
回答by GTCrais
@John Woo's answer helped me solve a similar problem. I've improved upon his answer by setting the correct ordering as well. This has worked for me:
@John Woo 的回答帮助我解决了类似的问题。我还通过设置正确的顺序改进了他的回答。这对我有用：
SELECT a.*, c.*
FROM users a
INNER JOIN payments c
ON a.id = c.user_ID
INNER JOIN (
SELECT user_ID, MAX(date) as maxDate FROM
(
SELECT user_ID, date
FROM payments
ORDER BY date DESC
) d
GROUP BY user_ID
) b ON c.user_ID = b.user_ID AND
c.date = b.maxDate
WHERE a.package = 1
I'm not sure how efficient this is, though.
不过，我不确定这有多有效。
回答by Jér?me B
My answer directly inspired from @valex very usefull, if you need several cols in the ORDER BY clause.
如果您在 ORDER BY 子句中需要多个列，我的回答直接灵感来自 @valex 非常有用。
SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*,
@num := if(@id = user_id, @num + 1, 1) as row_number,
@id := user_id as tmp
FROM (SELECT * FROM payments ORDER BY p.user_id ASC, date DESC) AS p,
(SELECT @num := 0) x,
(SELECT @id := 0) y
)
ON (p.user_id = u.id) and (p.row_number=1)
WHERE u.package = 1
回答by Shekhar
You can try this:
你可以试试这个：
SELECT u.*, p.*
FROM users AS u LEFT JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY [Date] DESC) AS RowNo
FROM payments
) AS p ON u.userid = p.userid AND p.RowNo=1
