今天再练习Mysql练习题的时候遇到了 “查询和01号的同学学习的课程完全相同的其他同学的信息” 的问题,开始一脸懵逼,那个带题目的大佬的答案貌似有点问题,所以网上看了一些大佬的代码,发现还不如理解别人的思路自己尝试,所以我写出了这样的sql,如果有问题,请大家及时留言帮我指正;
看了这个大佬讲的思路:https://blog.csdn.net/qq_36211042/article/details/102731433
#完整sql
SELECT s.* FROM student AS s WHERE SId IN(
SELECT sc2.SId FROM
(SELECT * FROM sc WHERE SId='01') AS sc1
JOIN
sc as sc2
ON
sc1.CId=sc2.CId
GROUP BY sc2.SId
HAVING
COUNT(*)=(SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId)
)
AND
SId<>'01';
这个问题我的思路如下:
#第一步我需要查询跟student条件相符的数据所以用IN
SELECT s.* FROM student AS s WHERE SId IN()
#第二步我需要得到01学生的信息
SELECT * FROM sc WHERE SId='01'
#第三步根据查询出的01学生的信息的SQL为子查询作为表1 使用内连接 连接整张成绩表(sc)得到结果集,然后分组
SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId
SELECT sc2.SId FROM
(SELECT * FROM sc WHERE SId='01') AS sc1
JOIN
sc as sc2
ON
sc1.CId=sc2.CId
#加入分组条件
SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId
SELECT sc2.SId FROM
(SELECT * FROM sc WHERE SId='01') AS sc1
JOIN
sc as sc2
ON
sc1.CId=sc2.CId
GROUP BY sc2.SId #分组条件根据学生ID分组
#加入分组后条件
SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId
SELECT sc2.SId FROM
(SELECT * FROM sc WHERE SId='01') AS sc1
JOIN
sc as sc2
ON
sc1.CId=sc2.CId
GROUP BY sc2.SId
HAVING #从这里开始
COUNT(*)=(SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId)
#结束!!!