查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名 的解决思路

[四川张三丰]

请老鸟帮帮我这个新鸟，整理一下这到题的解题思路


后面还有一到题是
查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；

[tseng_monkey]

查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名； 首先你得考虑“s001”同学所学的课程，查询出来，然后考虑，用exists去过滤就行 select 其他同学学号 , 姓名 from 学生表 a where not exists ( select 1 from 学生表 b , 成绩表 f where a.学号 = b.学号 and b.学号 = f.学号 and not exists ( select 1 from 学生表 c , 成绩表 d where c.学号 = b.学号 and f.学号=d.学号 and c.学号 = d.学号 and c.学号 = ‘s001’ ) );

[落落叶叶无声]

WITH T AS(
SELECT 'S001' AS SNO, 'C001' AS CNO FROM DUAL
UNION ALL
SELECT 'S002' AS SNO, 'C001' AS CNO FROM DUAL
UNION ALL
SELECT 'S003' AS SNO, 'C001' AS CNO FROM DUAL
UNION ALL
SELECT 'S004' AS SNO, 'C001' AS CNO FROM DUAL
UNION ALL
SELECT 'S001' AS SNO, 'C002' AS CNO FROM DUAL
UNION ALL
SELECT 'S002' AS SNO, 'C002' AS CNO FROM DUAL
UNION ALL
SELECT 'S003' AS SNO, 'C002' AS CNO FROM DUAL
UNION ALL
SELECT 'S001' AS SNO, 'C003' AS CNO FROM DUAL
)
SELECT DISTINCT SNO FROM T 
WHERE SNO != 'S001' --2.找出其他同学的课程
AND EXISTS --3.其他同学的课程是否存在与S001同学相同的，存在就X选出来
(SELECT 1 FROM 
(SELECT * FROM T WHERE SNO = 'S001') T1 --1.找出S001所学课程
WHERE T.CNO = T1.CNO)--3.其他同学的课程是否存在与S001同学相同的课程
--4.结果再与学生表连接

[sych888]

1、找到S001所学的课程 2、找到与S001所学课程有相同的学生 IN 3、找出学号和姓名 select su.sno,su.sname from student su where su.sno in ( select sc.sno from sc where sc.cno in (select s.cno from sc s where s.sno='s001') and sc.sno<>'s001')