38

50个SQL语句(MySQL版) 问题十二

 4 years ago
source link: https://www.maiyewang.com/archives/96577
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

--------------------------表结构--------------------------

student(StuId,StuName,StuAge,StuSex) 学生表

teacher(TId,Tname) 教师表

course(CId,Cname,C_TId) 课程表

sc(SId,S_CId,Score) 成绩表

----------------------------------------------------------

问题十二：查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名

SELECT st.StuId,st.StuName FROM student st
INNER JOIN sc ON st.StuId=sc.SId
WHERE sc.S_CId IN (SELECT S_CId FROM sc WHERE SId='1001')
GROUP BY st.StuId
HAVING COUNT(sc.S_CId)=(SELECT COUNT(*) FROM sc WHERE SId='1001')
AND st.StuId<>'1001';

答案仅供参考，不一定完全正确，若发现错误或有更好的，欢迎评论，互相交流，一起成长！！！


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK