相关文章推荐
心软的水煮肉  ·  欧文不做玻璃人-搜狐体育·  5 月前    · 
坚韧的充值卡  ·  齐齐哈尔医学院有保研资格吗_大学生必备网·  1 年前    · 
读研的莴苣  ·  极端武力最特殊作品 死神镰刀 少见的逆刃刀 ...·  2 年前    · 
谦虚好学的烤红薯  ·  在日本追人气较低的地下偶像是一种怎样的体验? ...·  2 年前    · 
冲动的梨子  ·  刘晓明大使夫妇出席2020年全英中国留学生春 ...·  2 年前    · 
小百科  ›  SQL内联视图子查阅-腾讯云开发者社区-腾讯云
select 数据库视图
气宇轩昂的铁板烧
3 年前
首页
学习
活动
专区
工具
TVP
返回腾讯云官网
提问
问 SQL内联视图子查询
Stack Overflow用户
提问于 2016-05-07 08:58:11
EN

是否可以从WHERE子句中的子查询引用" from“子句中定义的内联视图?

SELECT tmp.TeacherName,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM   (SELECT T.TeacherID              AS ID,
               T.TeacherName            AS Name,
               C.CourseID               AS CourseID,
               avg(L.AttendingStudents) AS AvgAttendingStudents
        FROM   Teachers AS T
               join Courses AS C
                 ON C.TeacherID = T.TeacherID
               join Lessons AS L
                 ON L.CourseID = C.CourseID
        GROUP  BY T.TeacherID,
                  C.CourseID) AS tmp
WHERE  tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                   FROM   tmp AS tmp2
                                   WHERE  tmp2.TeacherID = tmp.TeacherID);  

在这个例子中,我试图列出所有的老师,对于他们每一个人,我想显示出课程的最大平均学生人数(根据所有的课程计算)。我尝试使用内联视图(tmp)来计算每门课程的平均学员人数,但我不知道是否可以在子查询选择max(.)中引用该视图。我需要使用Oracle,但不幸的是,目前我没有任何Oracle数据库来尝试它。我尝试过使用MySQL (因为我不认为我使用的是任何特定于Oracle的特性),但正如预期的那样,我得到了“表'db.tmp‘不存在”的错误。这在甲骨文中是可能的吗?

下面是我的示例模式:

CREATE TABLE Courses
     CourseID   INTEGER PRIMARY KEY,
     CourseName VARCHAR(32),
     TeacherID  INTEGER
CREATE TABLE Teachers
     TeacherID   INTEGER PRIMARY KEY,
     TeacherName VARCHAR(32)
CREATE TABLE Lessons
     LessonDate        TIMESTAMP,
     CourseID          INTEGER,
     AttendingStudents INTEGER,
     PRIMARY KEY (LessonDate, CourseID)
  );  

(对不起,我的英语很差)

2 2.2K 0 票数 0
EN
sql
oracle
subquery
inline-view

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-05-07 09:13:50

您是正确的,您不能以这种方式引用派生表(“内联视图”)。您需要将派生表(“内联视图”)重写为公共表表达式:

你还有其他错误在里面。在派生表中,将 TeacherID 重命名为 ID ,将 TeacherName 重命名为 Name ,因此需要使用这些列名,而不是“真实的”列名。

此外,Oracle不支持表别名的 AS ,因此您也需要消除这些别名。

因此,对该声明的直接重写将是:

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) AS AvgAttendingStudents
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
  GROUP  BY T.TeacherID,
            C.CourseID
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

但是,由于 和聚合函数的无效使用,上述 在 group by 中不能工作。以上结果将导致"ORA-00979:不是按表达式分组“,请参见此 SQLFiddle 。

为此,您需要在CTE中使用一个窗口函数,并去掉 group by :

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) over (partition by t.teacherid, c.courseid) AS avgattendingstudents 
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

有关示例,请参阅此 SQLFiddle 。

请注意,您不能用MySQL测试上述查询,因为它不支持像普通表表达式或窗口函数一样的 现代SQL 。

但是您可以使用SQLFiddle示例来用数据测试它。

票数 1
EN

Stack Overflow用户

发布于 2016-05-07 09:04:01

您可以使用which子句,它可以为您提供一种限制agregate函数的方法。

这里有一个例子:

    SELECT T.TeacherID              AS ID,
           T.TeacherName            AS Name,
           C.CourseID               AS CourseID,
           avg(L.AttendingStudents) AS AvgAttendingStudents
    FROM   Teachers AS T
           join Courses AS C
             ON C.TeacherID = T.TeacherID
           join Lessons AS L
             ON L.CourseID = C.CourseID
 
推荐文章
心软的水煮肉  ·  欧文不做玻璃人-搜狐体育
5 月前
坚韧的充值卡  ·  齐齐哈尔医学院有保研资格吗_大学生必备网
1 年前
读研的莴苣  ·  极端武力最特殊作品 死神镰刀 少见的逆刃刀 斩滕断木小菜一碟|刀尖|刀鞘|刀柄_网易订阅
2 年前
谦虚好学的烤红薯  ·  在日本追人气较低的地下偶像是一种怎样的体验? - 知乎
2 年前
冲动的梨子  ·  刘晓明大使夫妇出席2020年全英中国留学生春节联欢晚会_中华人民共和国驻大不列颠和北爱尔兰联合王国
2 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
小百科 - 百科知识指南
© 2024 ~ 沪ICP备11025650号