如何根据联接查询中的一列返回最大行？

Question

我有一个查询，它从几个表返回带有一些ID的结果集。如果一个获得两个或多个行具有相同的QuestionID，我只想要一个具有MAX(QuestionSessionID)的行。我怎样才能做到这一点？

我尝试了许多不同的子查询变体，但都没有成功。如何做到这一点？

查询：

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON QS.UserID = 3 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND AQS.QuestionSessionID = QS.ID
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;

当前结果集：

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID
294441            112        121                1       25
22942             112        121                0       25
22942             126        141                1       39
131489            216        257                1       102
22942             222        263                1       106
22942             227        271                1       110
294435            760        955                1       5
294435            760        956                1       5

因此，在上面的示例中，我只想要一个具有QuestionID 112的行(最大(QuestionSessionID)294441的行)，如下所示：

期望的结果集：

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID
294441            112        121                1       25
22942             126        141                1       39
131489            216        257                1       102
22942             222        263                1       106
22942             227        271                1       110
294435            760        955                1       5
294435            760        956                1       5

更新：尝试添加另一个由评论者建议的连接，但没有正确。它似乎只在具有多个相同QuestionID之一的行上工作：

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID, MaxId
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON QS.UserID = 3 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND AQS.QuestionSessionID = QS.ID
/*AND AQS.QuestionSessionID = MaxId*/
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID MaxId
294441            112        121                1       25            294441
22942             112        121                0       25            294441
22942             126        141                1       39            293891
131489            216        257                1       102           294071
22942             222        263                1       106           294013
22942             227        271                1       110           294013
294435            760        958                1       5             294435
294435            760        959                1       5             294435
294435            760        955                1       5             294435
294435            760        956                1       5             294435
294435            760        957                1       5             294435
294435            771        970                1       241           294435
294435            771        971                1       241           294435
294435            771        972                1       241           294435
294435            776        978                1       245           294435
131489            962        1205               1       318           293592

更新2:

我根据一位评论员的帮助进行了一次小小的修改，让它如愿以偿：

工作查询：

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON  AQS.QuestionSessionID = QS.ID 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession AQS2
      JOIN QuestionSession QS2 ON AQS2.QuestionSessionID = QS2.ID
      WHERE QS2.UserID = 3
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND QS.UserID = 3 
AND AQS.QuestionSessionID = MaxId;

Answer 1

对AnswerQuestionSession表上的子查询添加一个联接

JOIN (SELECT QuestionID, MAX(QuestionSessionID as MaxId)
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq. QuestionID = Aqs. QuestionID

然后在WHERE子句中使用它

AND Aqs.QuestionSessionID = MaxId

这是基于更新的问题，我在QuestionSession上更改了联接，并确保在WHERE子句中使用了MaxId。

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON  AQS.QuestionSessionID = QS.ID
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND QS.UserID = 3 
AND AQS.QuestionSessionID = MaxId
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;