Question

SQL多表分组查询

有两张表：Student和Score

如何按照id分组，查询出每个id最大的语文成绩，查处后的表格应该是这样的：
id name address Chinese English
1 Jack Nanjing 78 61
2 Tom Beijing 98 58
3 Kitty Shanghai 92 75
4 Rose Suzhou 93 52

附上SQL插入语句：
create database Test
use Test
create table Student
(
id int,
name varchar(20),
address varchar(20)
)
create table Score
(
id int,
Chinese int,
English int
)
insert into Student values(1,'Jack','Nanjing')
insert into Student values(2,'Tom','Beijing')
insert into Student values(3,'Kitty','Shanghai')
insert into Student values(4,'Rose','Suzhou')

insert into Score values(1,59,63)
insert into Score values(1,69,62)
insert into Score values(1,78,61)
insert into Score values(1,70,60)
insert into Score values(2,41,59)
insert into Score values(2,98,58)
insert into Score values(2,32,57)
insert into Score values(2,54,56)
insert into Score values(3,65,78)
insert into Score values(3,63,77)
insert into Score values(3,78,76)
insert into Score values(3,92,75)
insert into Score values(4,52,54)
insert into Score values(4,65,53)
insert into Score values(4,93,52)
insert into Score values(4,77,51)





Answer 1

select t1.*,t2.English
from
(select a.id,a.name,a.address,MAX(b.Chinese) Chinese from student a,Score b
where a.id=b.id group by a.id,a.name,a.address) t1,
score t2
where t1.id=t2.id and t1.Chinese=t2.Chinese