SQL join: wybieranie ostatnich rekordów w relacji jeden do wielu

Możesz również spróbować zrobić to za pomocą sub select

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

Wybierz powinien dołączyć do wszystkich klientów i ich Ostatnia Data zakupu.

Innym podejściem byłoby użycie warunku NOT EXISTS w Twoim warunku przyłączenia do testów dla późniejszych zakupów:

SELECT *
FROM customer c
LEFT JOIN purchase p ON (
       c.id = p.customer_id
   AND NOT EXISTS (
     SELECT 1 FROM purchase p1
     WHERE p1.customer_id = c.id
     AND p1.id > p.id
   )
)

Nie podałeś bazy danych. Jeśli jest to takie, które umożliwia funkcje analityczne, może być szybsze użycie tego podejścia niż grupa po jednym(zdecydowanie szybciej w Oracle, najprawdopodobniej szybciej w późnych edycjach SQL Server, Nie wiem o innych).

Składnia w SQL Server będzie:

SELECT c.*, p.*
FROM customer c INNER JOIN 
     (SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
             FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1

Znalazłem ten wątek jako rozwiązanie mojego problemu.

With MaxDates as (
SELECT  customer_id,
                MAX(date) MaxDate
        FROM    purchase
        GROUP BY customer_id
)

SELECT  c.*, M.*
FROM    customer c INNER JOIN
        MaxDates as M ON c.id = M.customer_id 

Jeśli używasz PostgreSQL, możesz użyć DISTINCT ON, aby znaleźć pierwszy wiersz w grupie.

SELECT customer.*, purchase.*
FROM customer
JOIN (
   SELECT DISTINCT ON (customer_id) *
   FROM purchase
   ORDER BY customer_id, date DESC
) purchase ON purchase.customer_id = customer.id

PostgreSQL Docs-Distinct On

Zauważ, że DISTINCT ON pole(pola) -- here customer_id -- musi pasować do lewej większości pól w ORDER BY klauzuli.

Zastrzeżenie: jest to klauzula niestandardowa.

Spróbuj tego, to pomoże.

Użyłem tego w moim projekcie.

SELECT 
*
FROM
customer c
OUTER APPLY(SELECT top 1 * FROM purchase pi 
WHERE pi.customer_id = c.Id order by pi.Id desc) AS [LastPurchasePrice]

Testowane na SQLite:

SELECT c.*, p.*, max(p.date)
FROM customer c
LEFT OUTER JOIN purchase p
ON c.id = p.customer_id
GROUP BY c.id

Funkcja agregująca max() upewni się, że ostatni zakup zostanie wybrany z każdej grupy (ale zakłada, że kolumna daty jest w formacie, w którym max() podaje ostatni - co zwykle ma miejsce). Jeśli chcesz obsługiwać zakupy z tą samą datą, możesz użyć max(p.date, p.id).

Jeśli chodzi o indeksy, użyłbym indeksu przy zakupie z (customer_id, date, [inne kolumny zakupu, które chcesz zwrócić w swoim select]).

The LEFT OUTER JOIN (w przeciwieństwie do INNER JOIN) upewni się, że klienci, którzy nigdy nie dokonali zakupu, są również uwzględniani.

Proszę spróbować tego,

SELECT 
c.Id,
c.name,
(SELECT pi.price FROM purchase pi WHERE pi.Id = MAX(p.Id)) AS [LastPurchasePrice]
FROM customer c INNER JOIN purchase p 
ON c.Id = p.customerId 
GROUP BY c.Id,c.name;

Nie wchodząc najpierw w kod, logika / algorytm idzie poniżej:

1. Przejdź do tabeli transaction z wieloma rekordami dla tego samego client.

2. Wybierz rekordy clientID i latestDate aktywności klienta za pomocą group by clientID i max(transactionDate)

      select clientID, max(transactionDate) as latestDate 
      from transaction 
      group by clientID
   

3. inner join Tabela transaction z wynikiem z Kroku 2, wtedy będziesz miał pełne rekordy tabeli transaction z tylko najnowszym rekordem każdego klienta.

      select * from 
      transaction t 
      inner join (
        select clientID, max(transactionDate) as latestDate
        from transaction 
        group by clientID) d 
      on t.clientID = d.clientID and t.transactionDate = d.latestDate) 
   

4. Możesz użyć wynik z kroku 3, aby dołączyć do dowolnej tabeli, którą chcesz uzyskać różne wyniki.