T-SQL 子查询 Max(Date) 和 Joins
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/879111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Subquery Max(Date) and Joins
提问by MaxGeek
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/2005
1 1/1/2007
1 1/1/2009
2 1/1/2005
2 1/1/2006
2 1/1/2008
3 1/1/2008
3 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.
我该怎么做才能得到我想要的结果。
回答by wcm
Try this:
尝试这个：
Select *,
Price = (Select top 1 Price
From MyPrices
where PartID = mp.PartID
order by PriceDate desc
)
from MyParts mp
回答by Tom H
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 的价格，这将给出多个结果（大多数其他解决方案也会这样做）。此外，我没有什么可解释的最后价格日期是在未来。无论您最终使用哪种方法，您都可能需要考虑对此进行检查。
回答by KM.
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
回答by A-K
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
回答by u07ch
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
回答by Andomar
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!
PS改用wcm的解决方案！
回答by gbn
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 的最新价格日期（标准汇总），然后将其重新加入以获取价格（不能在汇总中），然后获取零件详细信息。
回答by Eduardo Molteni
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)
回答by Rupal
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)
回答by Suren
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 获取 max pricedate 然后，使用 partid 和max_priceate与 MyParts 内部连接
