列出班级名称时每个班级的学生人数 [英] count of students in each class while listing class names

问题描述

我有2张桌子

类
学生

每个学生记录中都有一个映射记录（比如说 class_id ）在 students 表，以便映射哪个学生属于哪个班级，现在我想列出所有班级记录以及与该班级相关的学生人数。

every student record will have a mapping record (lets say class_id) in students table, so that maps which student belongs to which class, now I want to list all class records along with the count of students associated with that class.

建议/解决方案。

我确定可以使用此解决方案，我尝试了搜索技巧，但没有运气，任何现有的方法问题/答案将是一个快速而有用的帮助。

Im sure a solution is available for this, I tried with my search skills but no luck, any existing question/answer would be a quick and great help.

推荐答案

设置（请参见 SqlFiddle ）：

create table classes (class_id int primary key, class_name text);
create table students (student_id int primary key, class_id int references classes);
insert into classes
select i, 'Class'|| i
from generate_series (1, 5) i;

insert into students
select i, floor(random()* 5+ 1)::int
from generate_series (1, 20) i;

在联接表上使用 count（）：

select class_id, class_name, count(student_id)
from classes c
join students s using(class_id)
group by 1, 2
order by 1

这篇关于列出班级名称时每个班级的学生人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！