文章目录
力扣高频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;
结果截图
