T-SQL 子查询 Max(Date) 和 Joins

2021-12-17 00:00:00 join sql subquery tsql sql-server

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

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.

以下是一些示例表:

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)

出错了.

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

What can I do to get the results I want.

推荐答案

试试这个:

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

相关文章