MySQL INNER JOIN wybierz tylko jeden wiersz z drugiej tabeli

SELECT u.*, p.*, max(p.date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id
ORDER BY p.date DESC

Zobacz to sqlfiddle

   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

Istnieją dwa problemy z zapytaniem:

1. każda tabela i subquery wymaga nazwy, więc musisz nazwać subquery INNER JOIN (SELECT ...) AS p ON ....
2. subquery jak masz to zwraca tylko jeden wiersz period, ale tak naprawdę chcesz jeden wiersz dla każdego użytkownika . W tym celu potrzebujesz jednego zapytania, aby uzyskać maksymalną datę, a następnie połączyć się z powrotem, aby uzyskać cały wiersz.

Zakładając, że nie ma powiązań dla payments.date, Spróbuj:

    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

Matei Mihai podał proste i efektywne rozwiązanie, ale nie zadziała, dopóki nie umieści MAX(date) w wybranej części, więc to zapytanie stanie się:

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

I order by nie robi żadnej różnicy w grupowaniu, ale może zamówić końcowy wynik podany przez group by. Próbowałem i zadziałało.

@John Woo ' s answer help me solve a similar problem. Poprawiłem jego odpowiedź, ustawiając poprawną kolejność. U mnie to działa:

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

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
)

To rozwiązanie jest lepsze niż zaakceptowana odpowiedź ponieważ działa poprawnie, gdy są jakieś płatności z tym samym użytkownikiem i datą.

Moja odpowiedź bezpośrednio zainspirowana przez @valex bardzo przydatna, jeśli potrzebujesz kilku cols w klauzuli ORDER BY.

    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