我有四个表: User、Version、VersionOfDocument、Document。ER看起来像这样:(粗体=主键)
我希望查询为每个documentID显示最近的日期、versionNr和对文档进行最后更改的人:
documentID modifiedDate Username VersionNr
1 1/4/2014 Alfred.Heymans@test.nl 2.0
2 2/5/2013 Alfred.Heymans@test.nl 1.0
3 4/5/2014 John.Smith@test.nl 2.0
发布于 2015-01-07 18:59:53
试试这个:
SELECT VD.DocumentID, V.modifiedDate, U.Username, V.VersionNr
FROM VersionOfDocument VD
INNER JOIN VERSION V ON VD.VersonId = V.VersionId
INNER JOIN (SELECT VD.DocumentID, MAX(V.modifiedDate) modifiedDate
FROM VersionOfDocument VD
INNER JOIN VERSION V ON VD.VersonID = V.VersionID
GROUP BY VD.DocumentID
) AS A ON VD.DocumentID = A.DocumentID AND V.modifiedDate = A.modifiedDate
INNER JOIN USER U ON V.userID = U.userID;
https://stackoverflow.com/questions/27817798
复制相似问题