三张表(学生表,课程表,成绩表)S(SID,SN)学号,姓名
C(CID,CN)课程号,课程名
SC(SID,CID,SCORE)学号,课程号,成绩查询:列出有两门(含)以上不及格课程的学生名单及其平均成绩
SELECT S.SID, S.SN, AVG(SC.SCORE)
FROM S, SC
WHERE S.SID IN
(
SELECT SC.SID FROM SC
WHERE SC.SCORE < 60
GROUP BY SC.SID
HAVING COUNT(SC.CID) >= 2
)
GROUP BY SC.SID这样写为什么不对呢?
应该怎么写呢?
在线等待... ...
C(CID,CN)课程号,课程名
SC(SID,CID,SCORE)学号,课程号,成绩查询:列出有两门(含)以上不及格课程的学生名单及其平均成绩
SELECT S.SID, S.SN, AVG(SC.SCORE)
FROM S, SC
WHERE S.SID IN
(
SELECT SC.SID FROM SC
WHERE SC.SCORE < 60
GROUP BY SC.SID
HAVING COUNT(SC.CID) >= 2
)
GROUP BY SC.SID这样写为什么不对呢?
应该怎么写呢?
在线等待... ...
FROM S, SC
WHERE S.SID IN
(
SELECT SC.SID FROM SC
WHERE SC.SCORE < 60
GROUP BY SC.SID
HAVING COUNT(SC.CID) >= 2
)
where s.sid=sc.sid
GROUP BY s.sn
试试
where s.sid=sc.sid
里的WHERE改为AND
-- 列出有两门(含)以上不及格课程的学生名单及其平均成绩
SELECT S.SID, S.SN, AVG(SC.SCORE)
FROM S, SC
WHERE S.SID IN
(
SELECT SC.SID FROM SC
WHERE SC.SCORE < 60
GROUP BY SC.SID
HAVING COUNT(SC.CID) >= 2
)
AND S.SID = SC.SID
GROUP BY S.SN不行,有错误 ORA-00979: 不是 GROUP BY 表达式
FROM S, SC
WHERE S.SID IN
(
SELECT SC.SID FROM SC
WHERE SC.SCORE < 60
GROUP BY SC.SID
HAVING COUNT(SC.CID) >= 2
)
and s.sid=sc.sid用笛卡尔积不对,换用自然连接
GROUP BY SC.SID
SELECT S.SID, S.SN, AVG(SC.SCORE)
FROM S,sc
WHERE s.sid=sc.sid
GROUP BY S.sid,s.sn
having count(case when sc.score<60 then 1 end)>=2