SQL统计学生在所有课程中的同班同学人数

Question

我有以下表格：

• 学生：学生ID（PK），学生姓名

我需要两个问题：

>

为学生表中的每个学生id计算该学生在所有课程中的不同（唯一）同学总数的查询。如果学生未注册任何课程，则应返回0。

例如，如果学生_ID 123参加了3门课程，并且每门课程都有10名不同的同学，我应该得到以下结果：

Student_ID        Num_of_classmates
-----------------------------------
123               30

返回所有学生及其所有同学的SQL查询。一个学生的同学是至少在同一个班级注册的另一个学生。

Answer 1

在这里，我只给出了第2部分的解决方案，因为@Gordon Linoff已经完成了第1部分），您也修复了-1问题。

一点：在这里使用internaljoin而不是left join，以避免在同学姓名中使用NULL。希望这也能增加一点帮助：）

测试数据集

if object_id('tempdb..#Student') is not null drop table #Student;
create table #Student (
    Student_ID int PRIMARY key,
    Student_Name varchar(50)
)
insert into #Student(Student_ID, Student_Name)
values (1,'Alice'), (2,'Bob'),(3,'Claire'),(4,'Danny'),(5,'Eve'),(6,'Frank');

if object_id('tempdb..#Course') is not null drop table #Course;
create table #Course (
    Course_ID int PRIMARY key,
    Course_Name varchar(50)
)
insert into #Course(Course_ID, Course_Name)
values (1,'Algorithm'), (2,'Bash'),(3,'Compiler'),(4,'Design Pattern'),(5,'Exception Handling');

if object_id('tempdb..#Enrollment') is not null drop table #Enrollment;
create table #Enrollment (
    Student_ID int,
    Course_ID int
)
insert into #Enrollment(Student_ID, Course_ID)
values (1,1),(1,3),
       (2,2),(2,3),
       (3,3),(3,4),
       (4,1),(4,4),
       (5,1),
       (6,5);  -- This Frank guy has no classmate

-- select * from #Student;
-- select * from #Course;
-- select * from #Enrollment;

解决方案（2）

select distinct
    A.Student_Name as Student_Name,
    D.Student_Name as Classmate_Name
from #Student as A
-- Student (A) -> Enrolled Courses (B)
inner join #Enrollment as B  
    on A.Student_ID = B.Student_ID
-- Enrollment Courses (B) -> Enrolled Students in that Course (C)
inner join #Enrollment as C  
    on B.Course_ID = C.Course_ID
    and B.Student_ID <> C.Student_ID  -- exclude self
-- Classmate's names
inner join #Student as D
    on C.Student_ID = D.Student_ID
order by Student_Name, Classmate_Name;

输出

注意：Frank没有同学，也没有显示空值。

| Student_Name | Classmate_Name |
|--------------|----------------|
| Alice        | Bob            |
| Alice        | Claire         |
| Alice        | Danny          |
| Alice        | Eve            |
| Bob          | Alice          |
| Bob          | Claire         |
| Claire       | Alice          |
| Claire       | Bob            |
| Claire       | Danny          |
| Danny        | Alice          |
| Danny        | Claire         |
| Danny        | Eve            |
| Eve          | Alice          |
| Eve          | Danny          |

Answer 2

目前尚不清楚您想要以下哪一项：

• 所有班级的学生总数（包括给定的学生）

在任何情况下，基本上都是两种连接和聚合：

select s.student_id,
       count(ec.student_id) as total_classmates,
       count(distinct s.student_id) as distinct_classmates,
       count(distinct case when ec.student_id <> s.student_id then ec.student_id end) as distinct_other_classmates
from student s left join
     enrollment e
     on e.student_id = s.student_id left join
     enrollment ec
     on ec.class_id = e.class_id;
group by s.student_id;