本文介绍了MySQL INNER JOIN 从第二个表中只选择一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
我有一个 users 表和一个 payments 表，对于每个有付款的用户，在 payments表.我想选择所有有付款的用户，但只选择他们最近的付款.我正在尝试这个 SQL，但我以前从未尝试过嵌套 SQL 语句，所以我想知道我做错了什么.感谢帮助
I have a users table and a payments table, for each user, those of which have payments, may have multiple associated payments in the payments table. 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
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
推荐答案
您需要有一个子查询来获取每个用户 ID 的最新日期.
You need to have a subquery to get their latest date per 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
这篇关于MySQL INNER JOIN 从第二个表中只选择一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！