编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	st_id int,
	name varchar(255),
	address varchar(255),
	city varchar(255),
    score int
);

INSERT INTO student VALUES (1, '张三', '李四家隔壁', '杭州', 88);
INSERT INTO student VALUES (2, '李四', '李四家隔壁', '杭州', 63);
INSERT INTO student VALUES (1, '张三',  '张三家隔壁', '北京', 91);
INSERT INTO student VALUES (2, '李四',  '张三家隔壁', '北京', 95);
INSERT INTO student VALUES (3, '王五', '李四家隔壁', '杭州', 78);
INSERT INTO student VALUES (3, '王五',  '张三家隔壁', '北京', 66);
INSERT INTO student VALUES (4, '马六', '李四家隔壁', '杭州', 80);
INSERT INTO student VALUES (4, '马六',  '张三家隔壁', '北京', 75);

create table class(
    id int,
    class_id int,
    student_id int,
    class_name varchar(255)
);

insert into class values
    (1, 1, 1, '高三(1)'),
    (2, 2, 2, '高三(2)'),
    (3, 2, 3, '高三(2)'),
    (4, 1, 4, '高三(1)');


SELECT * FROM student;
SELECT * FROM class;

-- 用子返回后,对应的列无法统计
-- select class_name,
-- sum(select count(*) from test.student as ts 
-- where ts.st_id=tc.student_id
-- group by ts.name) as total
-- from test.class as tc;
-- group by tc.class_id;



-- 方法一:再次包裹查询结果,然后对字段进行聚合
select class_name, sum(total)
from (
    select class_name,
        (select count(*) from test.student as ts 
         where ts.st_id=tc.student_id
         group by ts.name) as total
    from test.class as tc
    ) as class
group by class_name;

-- 方法二:用左连接统计
use test
select c.class_name, sum(s.total)
from class as c
left join (
    select st_id, count(*) total from  student 
    group by st_id
) as s
on s.st_id=c.student_id
group by c.class_name;