求一条sql

[caiselangren]

班级表（classes）：班级id(id) ,班级名（class_name）
学生表(student)：学生id（id），学生名（name）,班级id(class_id)

要查：学生id,name,班级名，所在班级人数
mysql 怎么写啊

[ACMAIN_CHM]

select 学生id,name,c.class_name,(select count(*) from student where class_id=s.class_id) as 所在班级人数
from student s ,classes c
where s.class_id=c.id

[rucypli]

select s.id,s.name,c.class_name,count(*)
from student s ,classes c
where s.class_id=c.id
group by s.id,s.name,c.class_name

[oracle_1010]

PG大家用的多么？

[wwwwb]

select 学生id,name,c.class_name,(select count(*) from student where class_id=A.class_id)
from student A INNER JOIN classes B ON A.class_id=B.id

[Rotel-刘志东]

select a.id,a.name,b.class_name,(select count(*) from student where class_id=A.class_id)as'班级人数' 
from student a
left join classes b
on a.id=b.class_id;

[caiselangren]

谢谢大家