SQL Join: Auswählen der letzten Datensätze in einer eins-zu-viele Beziehung

Question

Angenommen, ich habe eine Tabelle mit Kunden und eine Tabelle mit Einkäufen. Jeder Einkauf gehört zu einem Kunden. Ich möchte eine Liste aller Kunden zusammen mit ihrem letzten Kauf in einer SELECT-Anweisung erhalten. Was ist die beste Vorgehensweise? Irgendwelche Ratschläge zum Erstellen von Indizes?

Bitte verwenden Sie diese Tabellen-/Spaltennamen in Ihrer Antwort:

• Kunde: id, name
• Kauf: id, customer_id, item_id, date

Und in komplizierteren Situationen, wäre es (leistungsbedingt) vorteilhaft, die Datenbank zu denormalisieren, indem man den letzten Kauf in die Kundentabelle aufnimmt?

Wenn die (Kauf-) id garantiert nach Datum sortiert ist, können die Anweisungen durch die Verwendung von etwas wie LIMIT 1 vereinfacht werden?

Answer 1

Ich habe diesen Thread als Lösung für mein Problem gefunden.

Aber als ich sie ausprobiert habe, war die Leistung schlecht. Unten finden Sie meinen Vorschlag für eine bessere Leistung.

Mit MaxDates als (
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 

Hoffentlich wird das hilfreich sein.

Answer 2

Versuch es mal, es wird helfen.

Ich habe das in meinem Projekt verwendet.

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

Answer 3

Ich brauchte, was du brauchtest, wenn auch viele Jahre später, und probierte die beiden beliebtesten Antworten aus. Diese brachten nicht die gewünschten Früchte. Also das ist, was ich anzubieten habe... Zur Klarheit habe ich einige Namen geändert.

SELECT 
  cc.pk_ID AS pk_Customer_ID, 
  cc.Customer_Name AS Customer_Name, 
  IFNULL(pp.pk_ID, '') AS fk_Purchase_ID,
  IFNULL(pp.fk_Customer_ID, '') AS fk_Customer_ID,
  IFNULL(pp.fk_Item_ID, '') AS fk_Item_ID,
  IFNULL(pp.Purchase_Date, '') AS Purchase_Date
FROM customer cc
LEFT JOIN purchase pp ON (
  SELECT zz.pk_ID 
  FROM purchase zz 
  WHERE cc.pk_ID = zz.fk_Customer_ID 
  ORDER BY zz.Purchase_Date DESC LIMIT 1) = pp.pk_ID
ORDER BY cc.pk_ID;

Answer 4

Auf SQL Server könnten Sie verwenden:

SELECT *
FROM customer c
INNER JOIN purchase p on c.id = p.customer_id
WHERE p.id = (
    SELECT TOP 1 p2.id
    FROM purchase p2
    WHERE p.customer_id = p2.customer_id
    ORDER BY date DESC
)

SQL Server Beispiel: http://sqlfiddle.com/#!18/262fd/2

Auf MySQL könnten Sie verwenden:

SELECT c.name, date
FROM customer c
INNER JOIN purchase p on c.id = p.customer_id
WHERE p.id = (
    SELECT p2.id
    FROM purchase p2
    WHERE p.customer_id = p2.customer_id
    ORDER BY date DESC
    LIMIT 1
)

MySQL Beispiel: http://sqlfiddle.com/#!9/202613/7

Answer 5

Auf SQLite getestet:

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

Die max()-Aggregatfunktion stellt sicher, dass der neueste Kauf aus jeder Gruppe ausgewählt wird (geht davon aus, dass die Datumsspalte in einem Format vorliegt, bei dem max() das neueste Datum zurückgibt - was normalerweise der Fall ist). Wenn Sie Käufe mit demselben Datum behandeln möchten, können Sie max(p.date, p.id) verwenden.

In Bezug auf Indizes würde ich einen Index auf den Kauf mit (customer_id, date, [alle anderen Kaufspalten, die Sie in Ihrem SELECT zurückgeben möchten]) verwenden.

Das LEFT OUTER JOIN (im Gegensatz zum INNER JOIN) stellt sicher, dass auch Kunden, die nie einen Kauf getätigt haben, einbezogen werden.