这个查询语句可以简化吗?

[oXueYuZhiYing]

这个查询语句可以简化吗?

SELECT * FROM ICPrcPlyEntry AS Price INNER JOIN 
(SELECT  FItemID,FRelatedID,FCuryID,FUnitID,max(FBegDate) AS FBegDate FROM ICPrcPlyEntry 
GROUP BY  FItemID,FRelatedID,FUnitID,FCuryID) AS Ref 
ON Ref.FItemID=Price.FItemID AND Ref.FRelatedID=Price.FRelatedID AND
 Ref.FUnitID = Price.FUnitID AND Ref.FCuryID=Price.FCuryID AND Price.FBegDate=Ref.FBegDate

[發糞塗牆]

目测了一下，应该没有简化的必要，从性能上来说加点where条件之外，也没什么好改写的空间。改写了说不定性能更差

[阳泉酒家小当家]

你这个是求最近一个日期的数据是吧，我给格式化了一下，基本上你的语句已经很优化了：

SELECT * 
FROM ICPrcPlyEntry AS Price 
INNER JOIN 
(
SELECT  FItemID,FRelatedID,
        FCuryID,FUnitID,
        max(FBegDate) AS FBegDate 
FROM ICPrcPlyEntry 
GROUP BY  FItemID,FRelatedID,FUnitID,FCuryID
) AS Ref 
ON 
 Ref.FItemID=Price.FItemID AND 
 Ref.FRelatedID=Price.FRelatedID AND
 Ref.FUnitID = Price.FUnitID AND 
 Ref.FCuryID=Price.FCuryID AND 
 Price.FBegDate=Ref.FBegDate

[阳泉酒家小当家]

如果性能不好，可以尝试把inner join 改为inner hash join：

SELECT * 
FROM ICPrcPlyEntry AS Price 
INNER hash JOIN 
(
SELECT  FItemID,FRelatedID,
        FCuryID,FUnitID,
        max(FBegDate) AS FBegDate 
FROM ICPrcPlyEntry 
GROUP BY  FItemID,FRelatedID,FUnitID,FCuryID
) AS Ref 
ON 
 Ref.FItemID=Price.FItemID AND 
 Ref.FRelatedID=Price.FRelatedID AND
 Ref.FUnitID = Price.FUnitID AND 
 Ref.FCuryID=Price.FCuryID AND 
 Price.FBegDate=Ref.FBegDate

[oXueYuZhiYing]

好的谢谢。。。。。。。。。。。