請老鳥幫幫我這個新鳥，整理一下這到題的解題思路
后面還有一到題是
查詢至少學過學號為“s001”同學所有一門課的其他同學學號和姓名；
uj5u.com熱心網友回復：
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')
uj5u.com熱心網友回復：
查詢至少學過學號為“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’ ) );
uj5u.com熱心網友回復：
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.結果再與學生表連接
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107675.html
標籤：基礎和管理
上一篇：oracle 陳述句優化