查询只选修了"程序设计"和"数据库"两门课程的学生学号

游戏人间 2015-01-26 10:42:23

查询只选修了"程序设计"和"数据库"两门课程的学生学号

表介绍
STUDENT(S#,SNAME,SAGE,SSEX) -->学生表(学号,姓名,年龄,性别)
COURSE(C#,CNAME,T#) -->课程表(课程编号,课程名称,教师编号)
SC(S#,C#,SCORE) -->成绩表(学号,课程编号,成绩)
TEACHER(T#,TNAME) -->教师表(教师编号,教师名称)

...全文

1785 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

zebro1573 2015-02-04

打赏
举报

select st.id from student st inner join score sc on st.id=sc.id left join course co on sc.c_id=co.id where st.id in( select st.id from student st inner join score sc on st.id=sc.id left join course co on sc.c_id=co.id group by st.id having COUNT(st.id)>1 ) group by st.id

紫枫昵 2015-01-28

打赏
举报

select distinct s# from sc a left outer join course b on a.c#=b.c# where b.CNAME in('程序设计','数据库')--选出所有带有程序设计,数据库的人 and a.s# in (select s# from sc group by s# having count(S#)=2)--在这些人中，只有二条的人

hepe00 2015-01-28

打赏
举报

调整：

SELECT * 
FROM STUDENT AS S
WHERE NOT EXISTS(SELECT 1 FROM SC AS SC
					JOIN COURSE AS C ON SC.C#=C.C# 
					WHERE S.S#=SC.S# AND CNAME NOT IN('程序设计','数据库'))
	AND EXISTS(SELECT 1 FROM SC AS SC
				WHERE S.S#=SC.S#
				GROUP BY SC.S#
				HAVING COUNT(SC.C#)=2)

hepe00 2015-01-28

打赏
举报

引用 4 楼 wonderfuly 的回复:

这道题真的还是有难度。你们最好弄一些数据测试一下。目测了一下一楼可能是正确的。其它都错了。

这个题目，使用逆反的思维就清楚了，你的命题反过来就是：不存在选课不在“程序设计”和“数据库”的学生。 --STUDENT(S#,SNAME,SAGE,SSEX) -->学生表(学号,姓名,年龄,性别) --COURSE(C#,CNAME,T#) -->课程表(课程编号,课程名称,教师编号) --SC(S#,C#,SCORE) -->成绩表(学号,课程编号,成绩) ;WITH STUDENT(S#,SNAME,SAGE,SSEX) AS ( SELECT '1001','A',20,'Boy' union all select '1002','B',23,'Girl' union all select '1003','C',25,'Girl' ) ,COURSE(C#,CNAME,T#) as ( select 'C001','程序设计','t001' union all select 'C002','数据库','t001' union all select 'C003','数学','t001' union all select 'C004','语文','t001' union all select 'C005','地理','t001' union all select 'C006','计算机','t001' union all select 'C007','物理','t001' union all select 'C008','化学','t001' ) ,SC(S#,C#,SCORE) as ( select '1001','C001',60 UNION ALL select '1001','C002',60 UNION ALL select '1002','C001',60 UNION ALL select '1002','C002',60 UNION ALL select '1001','C005',60 UNION ALL select '1003','C008',60 ) SELECT * FROM STUDENT AS S WHERE NOT EXISTS(SELECT 1 FROM SC AS SC JOIN COURSE AS C ON SC.C#=C.C# WHERE S.S#=SC.S# AND CNAME NOT IN('程序设计','数据库')) --输出---------------- ---- S# SNAME SAGE SSEX ---- ----- ----------- ---- 1002 B 23 Girl

江南小鱼 2015-01-28

打赏
举报

with COURSE(C#,CNAME,T#) as
(
 select 10001,'数据库','01' union all
 select 10002,'程序设计','02' union all
 select 10003,'C++编程','03' union all
  select 10004,'Java程序设计','05' union all
   select 10005,'软件工程','04' union all
    select 10006,'科学发展观','06' 
),SC(S#,C#,SCORE) as
(
 select '2001',10001,90 union all
 select '2002',10001,40 union all
 select '2009',10003,78 union all
 select '2003',10003,60 union all
 select '2011',10006,100 union all
 select '2002',10002,60 union all
 select '2045',10002,68 union all
 select '2008',10004,98 union all
 select '2100',10006,76 union all
 select '2102',10001,53
)
select y.S#
from COURSE x,SC y
where x.C# = y.C# and x.CNAME IN('数据库','程序设计')
GROUP BY y.S# HAVING COUNT(y.S#) = 2

江南小鱼 2015-01-28

打赏
举报

select x.S#
from COURSE x,SC y
where x.C# = y.C# and x.CNAME IN('数据库','程序设计')
GROUP BY x.S# HAVING COUNT(x.S#) = 2

江南小鱼 2015-01-28

打赏
举报

你的问题就是查询：仅有课程设计和数据库两门课成绩的学生

select S# 
from SC x,
 (select c#,COUNT(*) cou from COURSE WHERE CNAME in ('程序设计','数据库') GROUP BY c# HAVING COUNT(1) = 2)  y
 where x.c#=y.C#

游戏人间 2015-01-27

打赏
举报

这道题真的还是有难度。你们最好弄一些数据测试一下。目测了一下一楼可能是正确的。其它都错了。

xxfvba 2015-01-27

打赏
举报

select S# from SC a, (select c#,COUNT(*) cou from course where cname in ('程序设计','数据库') group by c# having COUNT(*)=2)b where a.c#=b.C# group by S# having COUNT(*)=2

hepe00 2015-01-26

打赏
举报

SELECT * 
FROM STUDENT AS S
WHERE NOT EXISTS(SELECT 1 FROM SC AS SC 
				JOIN COURSE AS C ON SC.C#=C.C# 
				WHERE S.S#=SC.S# AND CNAME NOT IN('程序设计','数据库'))

Neo_whl 2015-01-26

打赏
举报


    select s# from student s
                        where exists(
                        select s# from sc 
                                         where exists(select c# from course c where c#=sc.c# and cname in ('程序设计','数据库管理') ) and s#=s.s# group by s# having count(c#)=2)

还在加载中灬 2015-01-26

打赏
举报

SELECT S.S#
FROM STUDENT S
	JOIN SC ON S.S#=SC.S#
	JOIN COURSE C ON SC.C#=C.C#
GROUP BY S.S#
HAVING SUM(CASE WHEN C.CNAME IN('程序设计','数据库') THEN 1 END)=2
	AND COUNT(C.C#)=2

另外，还可以用EXISTS NOT EXISTS