力扣高频SQL 50题（基础版）第二十题

力扣高频SQL 50题（基础版）第二十题

2356.每位教师所教授的科目种类的数量

题目说明

表: Teacher

±------------±-----+

| Column Name | Type |

±------------±-----+

| teacher_id | int |

| subject_id | int |

| dept_id | int |

±------------±-----+

在 SQL 中，(subject_id, dept_id) 是该表的主键。

该表中的每一行都表示带有 teacher_id 的教师在系 dept_id 中教授科目 subject_id。

查询每位老师在大学里教授的科目种类的数量。

思路分析

实现过程

准备数据

Create table If Not Exists Teacher (teacher_id int, subject_id int, dept_id int)
Truncate table Teacher
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '3')
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '4')
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '3', '3')
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '1', '1')
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '2', '1')
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '3', '1')
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '4', '1')

实现方式

select teacher_id,count(distinct subject_id) cnt from Teacher group by teacher_id;

结果截图