SQL内联视图子查询
是否可以从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
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示例来用数据测试它。
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
GROUP BY T.TeacherID,
T.TeacherName
C.CoursesID
HAVING avg(L.AttendingStudents) = (SELECT max(AvgAttendingStudents)
FROM Teachers AS tmp2
WHERE tmp2.TeacherID = T.TeacherID);我只是删除了您的第一个嵌套查询，并将AvgAttendingStudents更改为avg(L.AttendingStudents) (因为您不能在with子句上使用变量)并在Group子句中添加所选的属性，我不测试，但这里是实现这个技巧的方法。
不要忘记添加您在group子句中选择的未聚类变量。
这里是关于拥有条款的文档。
https://stackoverflow.com/questions/37086546
复制相似问题
腾讯云开发者
