遇到的面试题，请大家参谋参谋
有以下三个表：
学生表A：StudentID, StudentName
课程表B：ClassID, ClassName
成绩表C：StudentID, ClassID, Score
写出以下条件的SQL：
① 名为“李四”的学生选修的所有课程
② 选修计算机原理且仅选修该一门课程的学生姓名
③ 至少有两门课程不及格的学生的所有课程成绩
我回来后重新做答的：
①
- SQL code
SELECT A.StudentName,B.ClassName FROM C INNER JOIN A ON A.StudentID=C.StudentID INNER JOIN B ON B.ClassID= C.ClassID WHERE A.StudentName='李四'
②
- SQL code
SELECT StudentID,ClassID INTO #Temp FROM C WHERE StudentID IN (SELECT StudentID FROM C GROUP BY StudentID HAVING COUNT(1) = 1)SELECT A.StudentName FROM #Temp D INNER JOIN A ON A.StudentID=D.StudentID INNER JOIN B ON B.ClassID= D.ClassID WHERE B.ClassName='计算机原理'DROP TABLE #Temp
③
- SQL code
SELECT StudentID,Score INTO #Temp1 FROM C WHERE Score < 60SELECT StudentID INTO #Temp2 FROM #Temp1 GROUP BY StudentID HAVING COUNT(1) >= 2SELECT A.StudentName,B.ClassName,C.Score FROM C INNER JOIN A ON A.StudentID=C.StudentID INNER JOIN B ON B.ClassID= C.ClassID WHERE C.StudentID IN (SELECT StudentID FROM #Temp2) ORDER BY C.StudentIDDROP TABLE #Temp1,#Temp2
请大家帮忙点评解答一下
[解决办法]
改改别名
- SQL code
-方法2可这样写select *from Binner join C as D on B.ClassID=D.ClassIDinner join A on A.StudentID=D.StudentIDwhere b.ClassName='计算机原理'and not exists(select 1 from C where StudentID=D.StudentID and ClassID<>D.ClassID)方法3select *from Binner join C as D on B.ClassID=D.ClassIDinner join A on A.StudentID=D.StudentIDwhere exists(select 1 from C where StudentID=A.StudentID and Score<60 having count(distinct ClassID)>1)
[解决办法]