首頁猿問 mysql查詢數(shù)據(jù)庫的成績高于其平...

mysql查詢數(shù)據(jù)庫的成績高于其平均成績的學生的姓名？寫出打部分來了，但是就是不對，來個大神幫我看看

MySQL

俠客島的含笑 2016-10-13 21:36:41

mysql> select sname from student,sc where student.sno=sc.sno and sc.sno not in?(select r.sno from (select sno,score from sc,course where sc.cno=course.cno and course.cname='數(shù)據(jù)庫') AS r INNER JOIN(select sno,avg(score) avg_grade FROM sc group by sno) AS t ON r.sno=t.sno and r.score<t.avg_grade);

查看完整描述

3 回答

已采納

一瞬兒光

TA貢獻178條經(jīng)驗獲得超70個贊

SELECT?sname?FROM?student?WHERE?sno?in(SELECT?sno?FROM?sc?WHERE?score?>=?(SELECT?avg(`score`)?FROM?sc?GROUP?BY?(SELECT?cno?FROM?course?WHERE?cname='數(shù)據(jù)庫')));

反對回復 2016-10-14

俠客島的含笑
看來我思路有問題。

回復 2016-10-14
俠客島的含笑
不對，你寫的不對，問題是大于他自身的平均分，0分

回復 2016-10-14

一瞬兒光

TA貢獻178條經(jīng)驗獲得超70個贊

SELECT?sname?from?student?WHERE?sno?in(select?temp.sno?FROM?(select?sno,avg(score)?as?avgs?FROM?sc?GROUP?BY?sno)?as?temp?where?temp.sno?in(SELECT?sc.sno?from?sc?where?sc.sno=temp.sno?and?sc.cno=(SELECT?course.cno?from?course?WHERE?cname='數(shù)據(jù)庫')?and?sc.score>=temp.avgs));

你的表述就有問題，還0分，無語?。?！這會再試試吧

1 反對回復 2016-10-14

俠客島的含笑
你水平這么高，已經(jīng)工作了嗎

回復 2016-10-14
一瞬兒光
恩，不是高，你得多琢磨

回復 2016-10-15

俠客島的含笑

TA貢獻552條經(jīng)驗獲得超285個贊

SELECT sname FROM student WHERE sno in(SELECT sno FROM sc WHERE cno=(SELECT cno FROM course WHERE cname='數(shù)據(jù)庫') and score>=(SELECT avg(`score`) FROM sc group BY sno));

反對回復 2016-10-14