mysql - 如何在连接查询中返回基于一列的最大行？

Question

这个问题已经有答案了:

Retrieving the last record in each group - MySQL (33 个回答)

Get records with max value for each group of grouped SQL results (19 个回答)

已关闭 4 年前。

我有一个查询返回一个结果集，其中包含来自几个表的一些 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 的行(MAX(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

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;