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

link管理

链接快照平台

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

相关文章推荐

焦虑的柳树 · Amazon Redshift ...· 2 周前 ·

憨厚的硬盘 · Optimizer Hints | ...· 2 周前 ·

痴情的跑步机 · UI 插件 API | Vue CLI· 1 周前 ·

悲伤的手电筒 · Chapter 7 ...· 5 天前 ·

聪明的红酒 · SQLServer视图管理中的四个限制条件_ ...· 5 天前 ·

深沉的黑框眼镜 · 冯小刚：王中军曾跟王健林赔礼道歉但人家没理_ ...· 5 月前 ·

光明磊落的玉米 · Content Page· 6 月前 ·

高兴的四季豆 · 奥里萨邦 | 不可思议的印度· 6 月前 ·

强悍的大蒜 · Android实时获取摄像头画面传输至PC端 ...· 1 年前 ·

星星上的钥匙 · Windows Server 2019 ...· 1 年前 ·

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用户