最近工作上遇到一个问题可简化成这个问题:查询只选修了某一门课程的学生,SQL怎么写?
先给出DDL
CREATE TABLE student_course_mapping (
student_name VARCHAR(64),
course_name VARCHAR(64)
);
-- John选了三门课
insert into public.student_course_mapping values ('John','Database');
insert into public.student_course_mapping values ('John','HTML');
insert into public.student_course_mapping values ('John','Javascript');
-- Jack选了两门课
insert into public.student_course_mapping values ('Jack','Database');
insert into public.student_course_mapping values ('Jack','HTML');
-- Leon只选了Database
insert into public.student_course_mapping values ('Leon','Database');
-- Raymond只选了HTML
insert into public.student_course_mapping values ('Raymond','HTML');
数据是这样
student_name | course_name |
John | Database |
John | HTML |
John | Javascript |
Jack | Database |
Jack | HTML |
Leon | Database |
Raymond | HTML |
要查找只选修了Database课程的学生,我的SQL是这样
select scm2.* from student_course_mapping scm2
where scm2.course_name ='Database'
and scm2.student_name in (
select scm.student_name from student_course_mapping scm
group by scm.student_name
having count(scm.student_name) = 1
)
运行结果是
Leon Database
你们有没有更好的建议呢?