SQL内联视图子查询开发者社区

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

道上混的领结 · 查询处理体系结构指南 - SQL ...· 1 月前 ·

风度翩翩的充值卡 · 以前版本的 Tableau Cloud ...· 1 月前 ·

刚毅的灭火器 · 通过V$SESSION视图查看当前数据库会话 ...· 2 周前 ·

温暖的键盘 · 第 6 周：为 DBA 提供的最佳前 20 ...· 2 周前 ·

霸气的胡萝卜 · Oracle中常见的33个等待事件小结-平凯数据库· 2 周前 ·

很酷的回锅肉 · Python --- ...· 8 月前 ·

小胡子的火锅 · Android ...· 9 月前 ·

个性的电池 · Image 類別 ...· 2 年前 ·

非常酷的红茶 · Kotlin 修炼手册（2）基本数据类型 - 掘金· 2 年前 ·

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);

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)
  );

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);

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);

    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

回答 2

Stack Overflow用户

Stack Overflow用户