MySQL INNER JOIN wybiera tylko jeden wiersz z drugiej tabeli


104

Mam userstabelę i paymentstabelę, dla każdego użytkownika, z którym mają płatności, mogą mieć wiele powiązanych płatności w paymentstabeli. Chciałbym wybrać wszystkich użytkowników, którzy mają płatności, ale wybrać tylko ostatnią płatność. Próbuję tego SQL, ale nigdy wcześniej nie próbowałem zagnieżdżonych instrukcji SQL, więc chcę wiedzieć, co robię źle. Doceń pomoc

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

Odpowiedzi:


149

Musisz mieć podzapytanie, aby uzyskać ostatnią datę na 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

1
@Fluffeh masz bardzo miłą odpowiedź Part 1 - Joins and Unions. :) dodany do zakładek!
John Woo,

1
Dzięki kolego, napisałem go tylko dla takich sytuacji, wyświetl szybką odpowiedź z odpowiednim kodem SQL, a następnie zasugeruj link do dogłębnego przeczytania tego potwora, aby ludzie mogli zrozumieć sugerowany kod. Planowanie dodania do niego w celu dalszej rozbudowy. Zapraszam do przyłączenia się, jeśli chcesz, powinienem wyskoczyć na skrzypce do kodu naprawdę ...
Fluffeh

@JohnWoo dzięki za odpowiedź, która zadziałała idealnie. Dzięki Fluffeh za pytania i odpowiedzi, przyjrzę się temu!
Wasim,

Myślę, że moja odpowiedź jest prostsza i szybsza, ponieważ używam tylko jednego sprzężenia wewnętrznego. Może się mylę?
Mihai Matei

2
Czy istnieje sposób wykonania tego zapytania bez sprzężeń wewnętrznych? Chcę zwrócić maksimum z tabeli c LUB uzyskać wartość null z powrotem, jeśli żadne wiersze nie pasują. Zmiana JOIN na LEFT JOIN nie działa oczywiście.
Scott,

32
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ą płatności z tym samym użytkownikiem i datą.


to jest dobre podejście, którego używasz. ale mam pytanie dotyczące pobierania jednego obrazu dla jednego produktu, takiego jak. Jeden produkt ma wiele (4) zdjęć, ale chcę wyświetlać tylko jedno zdjęcie przy tym produkcie.
Ali Raza

Nie sądzisz, że będzie bardzo wolno go używać? podczas przetwarzania każdego rekordu w zapytaniu podrzędnym dotyczącym sprzężenia wewnętrznego. Zaakceptowana odpowiedź może być najlepszą możliwą odpowiedzią po drobnych poprawkach.
Hamees A. Khan

@ HameesA.Khan, nie sprawdzałem wykonywania zapytań. Możesz mieć rację, ale przyjęte rozwiązanie powoduje połączenie trójwymiarowe, które może być również powolne. Obawiam się, że zmiana nie będzie drobna (to jest temat pytania).
Finesse

12
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

Sprawdź to sqlfiddle


6
limit 1Klauzula będzie tylko powrót 1 użytkownika, który nie jest co PO chce.
Fluffeh,

6
@MateiMihai, to nie działa. Zapytanie podaje tylko datę maksymalną, a nie cały wiersz z datą maksymalną. Widać to na skrzypcach: datekolumna różni się od max(p.date). Jeśli dodasz więcej kolumn w paymentstabeli (np. cost), Wszystkie te kolumny nie będą z wymaganego wiersza
zxcat

3
   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

2

Z Twoim zapytaniem wiążą się dwa problemy:

  1. Każda tabela i podzapytanie wymaga nazwy, więc musisz nadać nazwę podzapytaniu INNER JOIN (SELECT ...) AS p ON ....
  2. Podzapytanie, jakie masz, zwraca tylko jeden okres wiersza, ale w rzeczywistości chcesz, aby jeden wiersz dla każdego użytkownika . W tym celu potrzebujesz jednego zapytania, aby uzyskać maksymalną datę, a następnie samozłączenia z powrotem, aby uzyskać cały wiersz.

Zakładając, że nie ma żadnych powiązań 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

to jest dobre podejście, którego używasz. ale mam pytanie dotyczące pobierania jednego obrazu dla jednego produktu, takiego jak. Jeden produkt ma wiele (4) zdjęć, ale chcę wyświetlać tylko jedno zdjęcie przy tym produkcie.
Ali Raza

W moim przypadku znalazłem to najszybciej. Jedyną zmianą, jaką zrobiłem, jest dodanie klauzuli where w zapytaniu podrzędnym, aby filtrować wybrane dane użytkownika tylko wtedy, From payments as p Where p.user_id =@user_idgdy zapytanie wykonuje grupę według całej tabeli.
Vikash Rathee

2

Odpowiedź @John Woo pomogła mi rozwiązać podobny problem. Poprawiłem jego odpowiedź, ustawiając również prawidłową kolejność. To zadziałało dla mnie:

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

Nie jestem jednak pewien, jak wydajne jest to.


2
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

To sprawi, że będzie działać


1

Matei Mihai otrzymał proste i wydajne rozwiązanie, ale nie zadziała, dopóki nie MAX(date)wstawi części SELECT, 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

Kolejność według nie ma żadnego wpływu na grupowanie, ale może uporządkować wynik końcowy dostarczony przez grupę według. Spróbowałem i zadziałało.


1

Moja odpowiedź bezpośrednio zainspirowana @valex jest bardzo przydatna, jeśli potrzebujesz kilku kolumn 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

1

Możesz spróbować tego:

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

1
Chociaż ten fragment kodu może rozwiązać problem, dołączenie wyjaśnienia naprawdę pomaga poprawić jakość Twojego posta. Pamiętaj, że odpowiadasz na pytanie do czytelników w przyszłości, a osoby te mogą nie znać powodów, dla których zaproponowałeś kod.
Alessio
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.