MySQL中复杂的联接和MAX（）查询

debugcn 发表于 Dev

龙卷风3

我需要跨4个表进行联接，同时选择与一个人关联的最大（即最新）测试时间戳。对于班上的每个学生，我想查询他们最近的考试是什么，并获取其ID和时间戳

SELECT students.ref, 
       students.fname, 
       students.sname, 
       classes.name AS 'group', 
       tests.id, 
       max(tests.timestamp)
FROM tests, students, classlinks, classes 
WHERE tests.ref=students.ref AND 
       classlinks.ref=students.ref AND 
       classlinks.classid=29 AND 
       tests.grade=2 AND 
       tests.subject=2 
GROUP BY students.ref
ORDER BY students.sname ASC, students.fname ASC

看起来很完美：对于班上的每个学生，它都提供了他们最近一次考试的时间戳。不幸的是，与该时间戳关联的测试ID是错误的：它只是给出随机测试的测试ID。

如果我将“分组依据”更改为

GROUP BY students.ref, tests.id

然后查询将匹配正确的测试ID以更正时间戳，但是现在每个学生都有多个条目。有没有人有任何建议，这样我就可以为每个学生排成一排，并且正确的考试ID与最新的时间戳相匹配？任何帮助表示赞赏。谢谢。

表说明：

mysql> describe students;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| ref    | varchar(50) | NO   | UNI | NULL    |                |
| fname  | varchar(22) | NO   |     | NULL    |                |
| sname  | varchar(22) | NO   |     | NULL    |                |
| school | int(11)     | NO   |     | NULL    |                |
| year   | int(11)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe classes;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| subject | int(11)     | YES  | MUL | NULL    |                |
| type    | int(11)     | YES  |     | 1       |                |
| school  | int(11)     | YES  |     | NULL    |                |
| year    | int(11)     | YES  |     | NULL    |                |
| name    | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> describe classlinks;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| ref     | varchar(50) | YES  | MUL | NULL    |                |
| subject | int(11)     | YES  |     | NULL    |                |
| school  | int(11)     | YES  |     | NULL    |                |
| classid | int(11)     | YES  | MUL | NULL    |                |
| type    | int(11)     | YES  |     | 1       |                |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe tests;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
+------------+-------------+------+-----+-------------------+-----------------------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment              |
| subject    | int(11)     | YES  |     | NULL              |                             |
| ref        | varchar(22) | NO   | MUL | NULL              |                             |
| test       | int(3)      | NO   |     | NULL              |                             |
| grade      | varchar(22) | NO   |     | NULL              |                             |
| timestamp  | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                             |
| lastupdate | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

极客

我假设的组合(ref,timestamp)在tests表中是唯一的。这是我的解决方案，但我没有任何示例数据可对其进行验证。如果不正确，请发布示例数据，以便我对其进行测试。

更新

这是正在检查sqlfiddle的更新查询

SELECT students.ref, 
  students.fname, 
  students.sname, 
  classes.name AS 'group', 
  tests.id, 
  T.timestamp
FROM (select ref,max(timestamp) as timestamp from tests group by ref)as T
    natural join tests, students, classlinks, classes 
WHERE
 T.ref=students.ref AND 
 classlinks.ref=students.ref AND
 classlinks.classid=classes.id AND 
 classlinks.classid=29 AND 
 tests.grade=2 AND 
 tests.subject=2
ORDER BY students.sname ASC, students.fname ASC

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。