运动会比赛信息的数据库,有如下三个表:
运动员ATHLETE(运动员编号 Ano,姓名Aname,性别Asex,所属系名 Adep), 项目 ITEM (项目编号Ino,名称Iname,比赛地点Ilocation), 成绩SCORE (运动员编号Ano,项目编号Ino,积分Score)。
写出目前总积分最高的系名及其积分,SQL语句实现正确的是:( )
SELECT Adep,SUM(Score)FROM ATHLETE,SCORE
WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)>=ANY
(SELECT SUM(Score) FROM ATHLETE,SCORE
WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
SELECT Adep,SUM(Score)FROM ATHLETE,SCORE
WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)>=SOME
(SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
SELECT Adep,SUM(Score)FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score) IN
(SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
SELECT Adep,SUM(Score)FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)>=ALL
(SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
这两个都是用于子查询的
any 是任意一个
all 是所有
any表示有任何一个满足就返回true,all表示全部都满足才返回true