select a.* from (select student_id,subject_id,max(grade) from grade_table group by studentid,subject_id) a,grade_table b where a.student_id=b.student_id and a.subject_id=b.subject_id and a.grade=b.grade------------ select a.* from (select student_id,max(grade) from grade_table group by studentid) a,grade_table b where a.student_id=b.student_id and a.grade=b.grade
谢谢 kevinlee0755还有一个问题mysql> select id,name,val from test where id = 1 ; +------+------+------+ | id | name | val | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | -1 | 2 | | 1 | -1 | 5 | +------+------+------+ 4 rows in set (0.00 sec)mysql> select id,max(name),max(val) from test where id = 1 group by id; +------+-----------+----------+ | id | max(name) | max(val) | +------+-----------+----------+ | 1 | 2 | 5 | +------+-----------+----------+ 1 row in set (0.00 sec) 我想以max(name)=2为主,获得val 怎么做啊..
外面在加个select select * from test where (id,name) in(select id,max(name) name from test where id = 1 group by id)
select b.* from (select student_id,subject_id,max(grade) as grade from grade_table group by student_id,subject_id) a, grade_table b where a.student_id=b.student_id and a.subject_id=b.subject_id and a.grade=b.grade
select b.* from (select student_id,max(grade) as grade from grade_table group by student_id) a,grade_table b where a.student_id=b.student_id and a.grade=b.grade
(select student_id,subject_id,max(grade) from grade_table group by studentid,subject_id) a,grade_table b
where a.student_id=b.student_id and a.subject_id=b.subject_id and a.grade=b.grade------------
select a.* from
(select student_id,max(grade) from grade_table group by studentid) a,grade_table b
where a.student_id=b.student_id and a.grade=b.grade
+------+------+------+
| id | name | val |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | -1 | 2 |
| 1 | -1 | 5 |
+------+------+------+
4 rows in set (0.00 sec)mysql> select id,max(name),max(val) from test where id = 1 group by id;
+------+-----------+----------+
| id | max(name) | max(val) |
+------+-----------+----------+
| 1 | 2 | 5 |
+------+-----------+----------+
1 row in set (0.00 sec)
我想以max(name)=2为主,获得val 怎么做啊..
test where (id,name) in(select id,max(name) name from test where id = 1 group by id)
grade_table b
where a.student_id=b.student_id and a.subject_id=b.subject_id and a.grade=b.grade
select b.* from (select student_id,max(grade) as grade from grade_table group by student_id) a,grade_table b where
a.student_id=b.student_id and a.grade=b.grade