求一条sql
班级表(classes):班级id(id) ,班级名(class_name)
学生表(student):学生id(id),学生名(name),班级id(class_id)
要查:学生id,name,班级名,所在班级人数
mysql 怎么写啊
-
SQL code
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
-
SQL code
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
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
-
SQL code
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;