iteye_15896
iteye_15896
2011-03-03 10:30

SQL 子查询 请教

已采纳

先看一个例子,有如下3张表。


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分的学生的姓名,怎么查? 首先想到的就是子查询:
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条回答

  • scau1983ljb 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)

    点赞 评论 复制链接分享
  • scau1983ljb 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 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;

    点赞 评论 复制链接分享

相关推荐