MySQL INNER JOIN从第二个表中仅选择一行

Question

我有一个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

Answer 1

您需要有一个子查询，以获取每个日期的最新日期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