T-SQL Unterabfrage Max(Datum) und Joins

Question

Ich versuche, mehrere Tabellen zu verbinden, aber eine der Tabellen hat mehrere Datensätze für eine partid mit unterschiedlichen Daten. Ich möchte den Datensatz mit dem aktuellsten Datum abrufen.

Hier sind einige Beispieltabellen:

Table: MyParts
Partid   Partnumber   Description
1        ABC-123      Pipe
2        ABC-124      Handle
3        ABC-125      Light

Table: MyPrices
Partid   Price        PriceDate
1        $1           1/1/2005
1        $2           1/1/2007
1        $3           1/1/2009
2        $2           1/1/2005
2        $4           1/1/2006
2        $5           1/1/2008
3        $10          1/1/2008
3        $12          1/1/2009

Wenn ich nur den letzten Preis für ein bestimmtes Teil finden wollte, könnte ich das tun:

SELECT * FROM MyPrice WHERE PriceDate = (SELECT MAX(PriceDate) 
FROM MyPrice WHERE Partid = 1)

Ich möchte jedoch zuerst eine Verbindung herstellen und den korrekten Preis für alle Teile zurückerhalten, nicht nur für eines. Das habe ich versucht:

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE 
MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice)

Die Ergebnisse sind falsch, da das höchste Preisdatum der gesamten Tabelle genommen wird.

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE 
MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice WHERE MyPrice.Partid =   
MyParts.Partid)

Das ist ein Fehler.

Was kann ich tun, um die gewünschten Ergebnisse zu erzielen?

Answer 1

Hier ist eine weitere Möglichkeit, dies ohne Unterabfragen zu tun. Diese Methode ist oft leistungsfähiger als andere, daher lohnt es sich, beide Methoden zu testen, um zu sehen, welche die beste Leistung bringt.

SELECT
     PRT.PartID,
     PRT.PartNumber,
     PRT.Description,
     PRC1.Price,
     PRC1.PriceDate
FROM
     MyParts PRT
LEFT OUTER JOIN MyPrices PRC1 ON
     PRC1.PartID = PRT.PartID
LEFT OUTER JOIN MyPrices PRC2 ON
     PRC2.PartID = PRC1.PartID AND
     PRC2.PriceDate > PRC1.PriceDate
WHERE
     PRC2.PartID IS NULL

Dies führt zu mehreren Ergebnissen, wenn Sie zwei Preise mit demselben EXAKTEN Preisdatum haben (die meisten anderen Lösungen tun dasselbe). Außerdem wird nicht berücksichtigt, dass das letzte Preisdatum in der Zukunft liegt. Vielleicht möchten Sie eine Prüfung dafür in Betracht ziehen, unabhängig davon, welche Methode Sie am Ende verwenden.

Answer 2

Versuchen Sie dies:

Select *,
    Price = (Select top 1 Price 
             From MyPrices 
             where PartID = mp.PartID 
             order by PriceDate desc
            )
from MyParts mp

Answer 3

SELECT
    MyParts.*,MyPriceDate.Price,MyPriceDate.PriceDate
    FROM MyParts
        INNER JOIN (SELECT Partid, MAX(PriceDate) AS MaxPriceDate FROM MyPrice GROUP BY Partid) dt ON MyParts.Partid = dt.Partid
        INNER JOIN MyPrice ON dt.Partid = MyPrice.Partid AND MyPrice.PriceDate=dt.MaxPriceDate

Answer 4

In SQL Server 2005 und höher verwenden Sie ROW_NUMBER() :

SELECT * FROM 
    ( SELECT p.*,
        ROW_NUMBER() OVER(PARTITION BY Partid ORDER BY PriceDate DESC) AS rn
    FROM MyPrice AS p ) AS t
WHERE rn=1

Answer 5

Etwa so

SELECT * 
FROM MyParts 
LEFT JOIN 
(
SELECT MAX(PriceDate), PartID FROM MyPrice group by PartID
) myprice
 ON MyParts.Partid = MyPrice.Partid 

Wenn Sie Ihre Partid kennen oder sie einschränken können, fügen Sie sie in die Verknüpfung ein.

   SELECT myprice.partid, myprice.partdate, myprice2.Price, * 
    FROM MyParts 
    LEFT JOIN 
    (
    SELECT MAX(PriceDate), PartID FROM MyPrice group by PartID
    ) myprice
     ON MyParts.Partid = MyPrice.Partid 
    Inner Join MyPrice myprice2
    on myprice2.pricedate = myprice.pricedate
    and myprice2.partid = myprice.partid