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.02,1,2,80.03,1,3,80.04,2,3,70.05,2,2,60.06,2,1,70.07,3,1,75.08,3,2,85.09,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)这是一种相关子查询，也就是子查询用到了外部查询的信息，这样的查询性能会有问题。这样写也得到了一样的结果，不过感觉怪怪的。谁能解释它怎么工作的吗？网上查了说当有子查询的时候会先执行子查询在执行外部的查询。对于无关子查询，由于子查询的结果是确定的，与外面的查询无关，还好理解。但是对于相关子查询，由于子查询用到了外部查询的条件，在进行子查询的时候会遍历外部查询来确定子查询的结果，进而再去定外部查询的结果。不明白！

解决方案

不好意思，sql写漏了select s.snameFROM 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)###个人比较喜欢下面的写法把各科平均成绩查询出来作为一个表进行关联查询子查询的性能一般不太好SELECT s.snameFROM 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.cidWHERE c.cname='math' AND sc.score > (c_avg+1)###select tt.sname from student_course ss,course cc,student ttwhere 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.cidand tt.sid = ss.sid;

时间： 2024-11-17 05:31:10

SQL 子查询 请教的相关文章

sql子查询中列别名在主查询中无效

问题描述 sql子查询中列别名在主查询中无效 CREATE PROCEDURE dbo.query_test( @i_begin_time varchar(128) IN, /* 开始时间 / @i_end_time varchar(128) IN, / 结束时间 / @i_bussiness_tpe varchar(512) IN, / 类型 ) as declare @v_sql varchar(16384)/* 定义 需要执行的sql / declare @Rtime varchar(12

SQL子查询实例

  SQL子查询实例介绍: 子查询是在一个查询内的查询.子查询的结果被DBMS使用来决定包含这个子查询的高级查询的结果.在子查询的最简单的形式中,子查询呈现在另一条SQL语句的WHERE或HAVING子局内. 列出其销售目标超过各个销售人员定额综合的销售点. SELECT CITY FROM OFFICES WHERE TARGET > (SELECT SUM(QUOTA) FROM SALESREPS WHERE REP_OFFICES = OFFICE) SQL子查询一般作为WHERE子句或

关联-在线求大神解答，急SQL 子查询

问题描述 在线求大神解答,急SQL 子查询 我现在有两张表,表1和表2,表2里面有一个权限字段,权限1,权限2.权限3.我现在要用权限1的id 查出权限2,3和权限1有关联的ID.然后把这查出来的ID作为条件,在表1里面去查. select * from dbo.Ask_info where login_ID not in (select login_ID from dbo.Login where teacher_Contact in (select login_ID from dbo.Logi

关于SQL子查询中使用order by的问题

问题描述 最近回头看以前的数据库教材<数据库系统概论>人大 王珊 版的有一段叙述:"需要特别指出的是,子查询的select语句不能使用 order by子句,order by子句只能对最终查询结果排序"但是正好之前练习碰到个场景,就在子句中用了order by ,结果也是正确的.所以我很疑惑,是书上说错了还是什么原因下面是SQLselect userid from (select userid,min(createtime) from LT_BIZORDER t where

sql子查询语句

子查询是嵌套在SELECT.INSERT.UPDATE和DELETE语句的WHERE子句和HAVING子句中的SELECT 语句. 它也可以嵌套在另一个子查询中. SELECT语句中子查询的语法格式为: 1.[NOT]IN子查询 这种语句的执行分两个步骤:首先执行内部子查询,然后根据子查询的结果再执行外层查询. SELECT title=CONVERT(char(25),title) FROM titles WHERE title_id IN (SELECT title_id FROM titl

标识符-一个关于子查询的问题求解决！

问题描述 一个关于子查询的问题求解决! SELECT CASE ????????? WHEN T1.A = T2.A THEN ?????????? (SELECT T3.A FROM (SELECT 1 AS A FROM DUAL) T3 WHERE T2.A = T3.A) ????????? ELSE ?????????? 0 ??????? END AS TEST ?? FROM (SELECT 1 AS A FROM DUAL) T1, (SELECT 1 AS A FROM DUA

Oracle子查询

SQL> --问题:要查询工资比SCOTT高的员工信息 SQL> --1. 查询SCOTT的工资 SQL> select sal from emp where ename='SCOTT';        SAL                                                                      ----------                                                             

sql:除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询

执行sql语句: select * from ( select * from tab where ID>20 order by userID desc ) as a order by date desc 逻辑上看着挺对 但是报错: 除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图.内联函数.派生表.子查询和公用表表达式中无效. 只要我们在嵌套子查询视图里面加入: top 100 percent 即可 select * from ( select top 100 p

如何使用SQL Server嵌套子查询

很多SQL Server程序员对子查询(subqueries)的使用感到困惑,尤其对于嵌套子查询(即子查询中包含一个子查询).现在,就让我们追本溯源地探究这个问题. 有两种子查询类型:标准和相关.标准子查询执行一次,结果反馈给父查询.相关子查询每行执行一次,由父查询找回.在本文中,我将重点讨论嵌套子查询(nested subqueries)(我将在以后介绍相关子查询). 试想这个问题:你想生成一个卖平垫圈的销售人员列表.你需要的数据分散在四个表格中:人员.联系方式(Person.Contact)