MySql 子查询和最大或分组依据?

本文介绍了MySql 子查询和最大或分组依据?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有这张桌子:

ID STUDENT CLASS QUESTION ANSWER   TIME
 1   1      1       1       c     12:30
 2   1      1       1       d     12:36
 3   1      1       2       a     12:38
 4   2      1       1       b     11:24
 5   2      1       1       c     11:26
 6   2      1       3       d     11:35
 7   2      3       3       b     11:24

我正在尝试编写一个执行此操作的查询:

I'm trying to write a query that does this:

对于特定班级的每个学生，为每个问题选择最新的答案.

因此，选择类1"将返回:

So, choosing class "1" would return:

ID STUDENT CLASS QUESTION ANSWER   TIME
 2   1      1       1       d     12:36
 3   1      1       2       a     12:38
 5   2      1       1       c     11:26
 6   2      1       3       d     11:35

我尝试了子查询、连接和分组的各种组合，但没有任何效果.有什么想法吗?

I've tried various combinations of subqueries, joins, and grouping, but nothing is working. Any ideas?

推荐答案

您可以使用子查询来获取每个 QUESTION 的最新 ANSWER，然后将其用作一个派生表并连接回原始表:

You can use a sub-query to get most recent ANSWER per QUESTION, Then use this as a derived table and join back to the original table:

 SELECT m.*
 FROM mytable AS m
 INNER JOIN (
    SELECT STUDENT,  QUESTION, MAX(`TIME`) AS mTime
    FROM mytable
    WHERE CLASS = 1
    GROUP BY STUDENT, QUESTION
) AS d ON m.STUDENT = d.STUDENT AND m.QUESTION = d.QUESTION AND m.`TIME` = d.mTime
WHERE m.CLASS = 1

此处演示

这篇关于MySql 子查询和最大或分组依据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！