2011-03-03 10:30

# SQL 子查询 请教

```
CREATE TABLE course (
cid BIGINT,
cname VARCHAR(200)
);

CREATE TABLE student (
sid BIGINT,
sname VARCHAR(200)
);

CREATE TABLE student_course (
scid BIGINT,
sid BIGINT,
cid BIGINT,
score FLOAT
);

```

```
1,"english"
2,"math"
3,"computer"

1,"tom"
2,"john"
3,"jacky"
4,"mary"

1,1,1,90.0
2,1,2,80.0
3,1,3,80.0
4,2,3,70.0
5,2,2,60.0
6,2,1,70.0
7,3,1,75.0
8,3,2,85.0
9,3,3,95.0```

1. 先找到数学成绩的平均分
2. 再找大学这个平均分1分的学生

```
SELECT S.*, SC.SCORE, C.CNAME
FROM STUDENT AS S, STUDENT_COURSE AS SC, COURSE AS C
WHERE S.SID = SC.SID AND C.CID = SC.CID AND C.CNAME = 'math'
AND SC.SCORE > ((SELECT AVG(SC.SCORE)
FROM STUDENT_COURSE AS SC, COURSE AS C
WHERE C.CID = SC.CID AND C.CNAME = 'math') + 1)```

```
1 tom    80.0 math
3 jacky  85.0 math```

```
SELECT S.*, SC.SCORE, C.CNAME
FROM STUDENT AS S, STUDENT_COURSE AS SC, COURSE AS C
WHERE S.SID = SC.SID AND C.CID = SC.CID AND C.CNAME = 'math'
AND SC.SCORE > ((SELECT AVG(SC.SCORE)
FROM STUDENT_COURSE AS SC
WHERE C.CNAME = 'math') + 1)```

• 点赞
• 写回答
• 关注问题
• 收藏
• 复制链接分享
• 邀请回答

#### 3条回答

• p0程序员 10年前

不好意思，sql写漏了

select s.sname
FROM student_course AS sc
JOIN student AS s ON sc.sid=s.sid
JOIN course AS c ON sc.cid=c.cid
JOIN (SELECT cid,avg(score) score FROM student_course GROUP BY cid) AS c_avg ON sc.cid=c_avg.cid
WHERE c.cname='math' AND sc.score > (c_avg.score+1)

点赞 评论 复制链接分享
• p0程序员 10年前

个人比较喜欢下面的写法
把各科平均成绩查询出来作为一个表进行关联查询
子查询的性能一般不太好

SELECT s.sname
FROM student_course AS sc
JOIN student AS s ON sc.sid=s.sid
JOIN course AS c ON sc.cid=c.cid
JOIN (SELECT cid,avg(score) FROM student_course GROUP BY cid) AS c_avg ON sc.cid=c_avg.cid
WHERE c.cname='math' AND sc.score > (c_avg+1)

点赞 评论 复制链接分享
• ttt10816 10年前

select tt.sname from student_course ss,course cc,student tt
where ss.score >
(
select avg(sc.score)
from course c, student_course sc
where c.cid = sc.cid
and c.cname = 'math'
)
and cc.cname='math'
and ss.cid = cc.cid
and tt.sid = ss.sid;

点赞 评论 复制链接分享