求个SQL语句

Question

student表中有字段，classid,studentid,value,time四个字段
classid为班级号，studentid为学号，value为成绩,time为时间
例如：
classid           studentid               value                 time
1                    12                    98                  2009-08-01
2                     2                    78                  2009-08-01
1                    12                    66                  2009-08-05
3                    6                     85                  2009-08-06
2                     2                    69                  2009-08-23
1                     2                    45                  2009-08-04现在要查询所有学生最新的一条记录，sql语句怎么写？也就是查询最终结果是：
classid           studentid               value                 time
1                    12                    66                  2009-08-05
3                    6                     85                  2009-08-06
2                     2                    69                  2009-08-23
1                     2                    45                  2009-08-04

Answer 1

select a.classid,a.studentid,a.value,a.time from tt a
left join tt b on a.studentid=b.studentid and a.time<=b.time
group by a.classid,a.studentid,a.value,a.time
having count(b.time)=1

Answer 2

select * from student s
where not exist (select 1 from student where studentid=s.studentid and time>s.time);

Answer 3

select a.* from tt a where 
time in
(select time from tt where studentid=a.studentid order by time desc limie 1)

Answer 4

你的学号有重复的，这样要加上classid select * from student s
where not exist (select 1 from student where studentid=s.studentid and classid=s.classid and time>s.time);或者select s.*
from student s inner join (select classid,studentid,max(time) as mtime from student group by classid,studentid) b
on s.classid=b.classid and s.studentid=b.studentid and s.time=b.mtime

Answer 5

mysql> select * from student;
+---------+-----------+-------+------------+
| classid | studentid | value | time       |
+---------+-----------+-------+------------+
|       1 |        12 |    98 | 2009-08-01 |
|       2 |         2 |    78 | 2009-08-01 |
|       1 |        12 |    66 | 2009-08-05 |
|       3 |         6 |    85 | 2009-08-06 |
|       2 |         2 |    69 | 2009-08-23 |
|       1 |         2 |    45 | 2009-08-04 |
+---------+-----------+-------+------------+
6 rows in set (0.00 sec)
mysql> select t1.* from student t1 where not exists
    -> (select 1 from student where 
         studentid=t1.studentid and classid=t1.classid and time>t1.time);
+---------+-----------+-------+------------+
| classid | studentid | value | time       |
+---------+-----------+-------+------------+
|       1 |        12 |    66 | 2009-08-05 |
|       3 |         6 |    85 | 2009-08-06 |
|       2 |         2 |    69 | 2009-08-23 |
|       1 |         2 |    45 | 2009-08-04 |
+---------+-----------+-------+------------+
4 rows in set (0.00 sec)

Answer 6

SELECT a.* FROM tgh a WHERE 
`time`= 
(SELECT `time` FROM tgh WHERE studentid=a.studentid AND classid=a.classid ORDER BY `time` DESC LIMIT 1)orSELECT a.classid,a.studentid,a.value,a.time FROM tgh a 
LEFT JOIN tgh b ON a.studentid=b.studentid AND a.classid=b.classid AND a.time <=b.time 
GROUP BY a.classid,a.studentid,a.value,a.time 
HAVING COUNT(b.time)=1

Answer 7

谢谢ls几位
select 1 from student where studentid=s.studentid and classid=s.classid and time>s.time
这个好像mysql不支持select 1语法

Answer 8

为什么不试一下？mysql> select 1 from a;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)mysql>

Answer 9

select 1 
是选出数字1，不是第一列

Answer 10

student表中有字段，classid,studentid,value,time四个字段 
classid为班级号，studentid为学号，value为成绩,time为时间 
例如： 
classid          studentid              value                time 
1                    12                    98                  2009-08-01 
2                    2                    78                  2009-08-01 
1                    12                    66                  2009-08-05 
3                    6                    85                  2009-08-06 
2                    2                    69                  2009-08-23 
1                    2                    45                  2009-08-04 
--------------------------
select classid,studentid,value,max(time) from student group by classid,studentid

Answer 11

select student.*
from student,
(select classid,studentid,max(time) m 
from student 
group by classid,studentid) a
where student.classid=a.classid
and student.studentid=a.studentid
and student.time=a.m
这个是正确的

Answer 12

select student.*
from student,
(select classid,studentid,max(time) m 
from student 
group by classid,studentid) a
where student.classid=a.classid
and student.studentid=a.studentid
and student.time=a.m
这个是正确的

Answer 13

那就要建立索引了，你的索引情况
studentid、classid建立索引没有

Answer 14

这个需要看一下你的表中的索引了。要么根据你目前索引的情况来修改SQL语句，要么根据常用的SQL语句来设置索引。