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

相关文章推荐

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

谦逊的硬币 · react ...· 11 月前 ·

失落的炒饭 · 常见c语言编译错误解析(转) - ...· 2 年前 ·

酷酷的烤地瓜 · Access Java object ...· 2 年前 ·

爽快的莲藕 · 暫時性 OS 磁碟 - Azure ...· 2 年前 ·

很酷的柑橘 · Windows Sockets Error ...· 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用户