select a.name,count(*)
from tb_class a,tb_student b,tb_lesson c
where a.id=b.class
and b.id=c.student_id
group by a.name
from tb_class a,tb_student b,tb_lesson c
where a.id=b.class
and b.id=c.student_id
group by a.name
GO
CREATE TABLE tb_student
(id VARCHAR(4), name VARCHAR(100), class VARCHAR(4))
GO
CREATE TABLE tb_class
(id VARCHAR(4), name VARCHAR(20))
GO
CREATE TABLE tb_lesson
(stdent_id VARCHAR(4), lesson VARCHAR(4))
GO
INSERT INTO tb_student
SELECT '1' , 'aaa' , '1' UNION ALL
SELECT '2' , 'dsf' , '1' UNION ALL
SELECT '3' , 'geww' , '2' UNION ALL
SELECT '4' , 'hff' , '1' UNION ALL
SELECT '5' , 'ght' , '3'
GO
INSERT INTO tb_class
SELECT '1' , '一班' UNION ALL
SELECT '2' , '二班' UNION ALL
SELECT '3' , '三班'
GO
INSERT INTO tb_lesson
SELECT '1' , 'aaa' UNION ALL
SELECT '2' , 'bbb' UNION ALL
SELECT '3' , 'ccc' UNION ALL
SELECT '2' , 'ddd' UNION ALL
SELECT '1' , 'eee' UNION ALL
SELECT '2' , 'eee' UNION ALL
SELECT '5' , 'aaa'
GOSELECT tb_class.id,COUNT(*) FROM tb_lesson
INNER JOIN tb_student
ON tb_lesson.student_id=tb_student.id
INNER JOIN tb_class
ON tb_class.id=tb_student.class
GROUP BY tb_class.id