sql - 内连接上的多个主键

I need to count all rows with distinctive ItemID:s with the highest RevisionID on the item with that ItemID. Here's my code (relevant parts):

我需要计算具有独特ItemID的所有行:具有该ItemID的项目上具有最高RevisionID的s。这是我的代码(相关部分):

(
SELECT count(parent.ItemID) FROM item AS parent
INNER JOIN item AS child ON parent.ItemID = child.ItemID AND parent.RevisionID = Max(child.RevisionID)
WHERE parent.orderID = orders.orderID AND parent.StatusInfo != '2' 
) as notordered,  

The code works if you take out "AND parent.RevisionID = Max(child.RevisionID)", but then it takes all the revision of the items. I am aware that I can't use "Max" with INNER JOIN ON, but I couldn't find a way to get it in a way that it was consistent with the rule parent.ItemID = child.ItemID with a subquery. Please help.

如果您取出“AND parent.RevisionID = Max(child.RevisionID)”,则代码可以工作,但它会获取所有项目的修订。我知道我不能在INNER JOIN ON中使用“Max”,但我找不到一种方法来获得它与规则parent.ItemID = child.ItemID与子查询一致的方式。请帮忙。

Example:
Input
A- ID:1 RevID:2
B- ID:1 RevID:3
C- ID:2 RevID:4
Return:
B & C, because they have unique ID:s and the highest RevID with that ID

示例:输入A-ID:1 RevID:2 B-ID:1 RevID:3 C-ID:2 RevID:4返回:B&C,因为它们具有唯一ID:s和具有该ID的最高RevID

1 个解决方案

#1

The correct answer hinted by http://*.com/questions/17882073/mysql-select-max-date-inside-a-join-statement/17882188#17882188 was:

http://*.com/questions/17882073/mysql-select-max-date-inside-a-join-statement/17882188#17882188提示的正确答案是:

SELECT count(parent.ItemID) FROM item AS parent
JOIN (SELECT itemID, MAX(RevisionID) as MaxRevision FROM Item GROUP BY ItemID) as child ON parent.ItemID = child.ItemID AND parent.RevisionID = child.MaxRevision
WHERE parent.orderID = orders.orderID 
) as notordered,