T-SQL子查询最大（日期）和联接

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date.

我正在尝试连接多个表，但其中一个表有多个记录，用于具有不同日期的partid。我希望获得最近日期的记录。

Here are some example tables:

以下是一些示例表：

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

If I was just wanted to find the most recent price for a certain part I could do:

如果我只是想找到某个部分的最新价格，我可以做：

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

However I want to do a join first and get back the correct price for all parts not just one. This is what I have tried:

但是我想首先进行加入并获得所有部件的正确价格而不仅仅是一个。这是我尝试过的：

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

The results are wrong as it takes the highest price date of the entire table.

结果是错误的，因为它占用整个表的最高价格日期。

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

That errors out.

出错了。

What can I do to get the results I want.

我该怎么做才能得到我想要的结果。

10 个解决方案

#1

Try this:

尝试这个：

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

#2

Here's another way to do it without subqueries. This method will often outperform others, so it's worth testing both methods to see which gives the best performance.

这是在没有子查询的情况下执行此操作的另一种方法。这种方法通常会优于其他方法，因此值得测试两种方法，以确定哪种方法能够提供最佳性能。

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

This will give multiple results if you have two prices with the same EXACT PriceDate (Most other solutions will do the same). Also, I there is nothing to account for the last price date being in the future. You may want to consider a check for that regardless of which method you end up using.

如果您有两个具有相同EXACT PriceDate的价格，那么这将产生多个结果（大多数其他解决方案将执行相同的操作）。此外，我没有什么可以解释将来的最后价格日期。无论您最终使用哪种方法，都可能需要考虑检查。

#3

Try joining to the a subquery of MyPrice in order to retrieve MAX(PriceDate):

尝试加入MyPrice的子查询以检索MAX（PriceDate）：

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

#4

On 2005 use ROW_NUMBER():

在2005年使用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

#5

Something like this

像这样的东西

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

If you know your partid or can restrict it put it inside the join.

如果你知道你的partid或者可以限制它将它放在连接中。

   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

#6

SELECT
    *
FROM
    (SELECT MAX(PriceDate) AS MaxP, Partid FROM MyPrices GROUP BY Partid) MaxP 
    JOIN
    MyPrices MP On MaxP.Partid = MP.Partid AND MaxP.MaxP = MP.PriceDate
    JOIN
    MyParts P ON MP.Partid = P.Partid

You to get the latest pricedate for partid first (a standard aggregate), then join it back to get the prices (which can't be in the aggregate), followed by getting the part details.

您首先获得partid的最新定价（标准汇总），然后将其加入以获取价格（不能合计），然后获取零件详细信息。

#7

Join on the prices table, and then select the entry for the last day:

加入价格表，然后选择最后一天的条目：

select pa.partid, pa.Partnumber, max(pr.price)
from myparts pa
inner join myprices pr on pr.partid = pa.partid
where pr.PriceDate = (
    select max(PriceDate) 
    from myprices 
    where partid = pa.partid
)

The max() is in case there are multiple prices per day; I'm assuming you'd like to display the highest one. If your price table has an id column, you can avoid the max() and simplify like:

max（）用于每天有多个价格;我假设你想展示最高的一个。如果您的价格表有一个id列，您可以避免max（）并简化如下：

select pa.partid, pa.Partnumber, pr.price
from myparts pa
inner join myprices pr on pr.partid = pa.partid
where pr.priceid = (
    select max(priceid)
    from myprices 
    where partid = pa.partid
)

P.S. Use wcm's solution instead!

附：请改用wcm的解决方案！

#8

All other answers must work, but using your same syntax (and understanding why the error)

所有其他答案必须有效，但使用相同的语法（并理解错误原因）

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

#9

Please try next code example:

请尝试下一个代码示例：

select t1.*, t2.partprice, t2.partdate 
from myparts t1
join myprices t2 
on t1.partid = t2.partid
where partdate = 
(select max(partdate) from myprices t3 
where t3.partid = t2.partid group by partid)

#10

For MySQL, please find the below query:

对于MySQL，请查找以下查询：

select * from (select PartID, max(Pricedate) max_pricedate from MyPrices group bu partid) as a 
    inner join MyParts b on
    (a.partid = b.partid and a.max_pricedate = b.pricedate)

Inside the subquery it fetches max pricedate for every partyid of MyPrices then, inner joining with MyParts using partid and the max_pricedate

在子查询中，它为MyPrices的每个partyid提取最大价格，然后使用partid和max_pricedate与MyParts进行内部连接

#1