T-SQL子查询Max(Date)和联接 [英] T-SQL Subquery Max(Date) and Joins

问题描述

我正试图联接多个表，但是其中一个表具有针对具有不同日期的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

这篇关于T-SQL子查询Max(Date)和联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！