mysql - 三个相关表的记录数总和

Question

我有三个相关表:

• 类(class)
• 学生
• 老师

每个类(class)都由一位老师教授给许多学生。

我可以找到参加类(class)的学生人数:

SELECT c.id, count(*) FROM course as c, student as s
WHERE c.id = s.course_id
GROUP BY c.id;

我可以找到一位老师教授的所有类(class):

SELECT t.id, c.id FROM course as c, teacher as t
WHERE t.id = c.teacher_id;

我想知道每位老师教了多少学生。以下查询是否正确？

SELECT t.id, sum(x.count_s) 
FROM 
   (SELECT count(*) AS count_s FROM course as c2, student as s
      WHERE c2.id = s.course_id AND c2.id = c.id
      GROUP BY c2.id) as x, 
   course as c, teacher as t
WHERE t.id = c.teacher_id;

不幸的是，我不能直接测试它，因为这实际上是手头实际问题的简化。我需要找出哪个解决方案适用于简化的问题。

Answer 1

回答你的问题，没有。您不能引用 c.id在别名为 x 的内联 View 中.那应该会引发错误。

但是如果你删除它，那么你的查询有可能返回一个夸大的计数，由于半笛卡尔积，在别名为 x 的内联 View 之间。和 c .

因此需要重新定位谓词，并且您需要从 x 返回 c2.id (即将它添加到 SELECT 列表，您已经在 GROUP BY 中引用了它)。

这等同于您的查询，只是重写以替换逗号连接运算符并将连接谓词重新定位到 ON 子句。此声明等同于您的声明，无效):

SELECT t.id
     , SUM(x.count_s) 
  FROM ( SELECT count(*) AS count_s 
           FROM course c2
           JOIN student s
             ON c2.id = s.course_id
            AND c2.id = c.id        -- INVALID here
          GROUP
             BY c2.id
       ) x
 CROSS                              -- no join predicate 
  JOIN course c
  JOIN teacher t
    ON t.id = c.teacher_id

要解决这个问题，请将 c2.id 添加到 x 中的 SELECT 列表中，并重新定位该谓词。像这样:

SELECT t.id
     , SUM(x.count_s) 
  FROM ( SELECT count(*) AS count_s
              , c2.id                 -- added
           FROM course c2
           JOIN student s
             ON c2.id = s.course_id
       --   AND c2.id = c.id          -- relocated (removed from here)
          GROUP
             BY c2.id
       ) x
  JOIN course c
    ON x.id = c.id                    -- relocated (added here)
  JOIN teacher t
    ON t.id = c.teacher_id

假设id在 course 中是 UNIQUE 且 NOT NULL ，该查询将返回合理的计数(尽管零计数将“丢失”)。

要返回“零”计数，您需要使用外部联接。因为我总是喜欢使用 LEFT JOIN，所以最外层查询中的表/内联 View 需要重新排序:

SELECT t.id
     , IFNULL(SUM(x.count_s),0)
  FROM teacher t
  LEFT
  JOIN course c
    ON c.teacher_id = t.id
  LEFT
  JOIN ( SELECT count(*) AS count_s
              , c2.id                 -- added
           FROM course c2
           JOIN student s
             ON c2.id = s.course_id
       --   AND c2.id = c.id          -- relocated (removed from here)
          GROUP
             BY c2.id
       ) x
    ON x.id = c.id                    -- relocated (added here)

假设id是每个表上的 PRIMARY KEY(或等效的 UNIQUE 和 NOT NULL)，然后将返回“正确”计数。

没有必要包含 course内联 View 中的表别名为 x . GROUP BY s.course_id 就足够了。

SELECT t.id
     , IFNULL(SUM(x.count_s),0)
  FROM teacher t
  LEFT
  JOIN course c
    ON c.teacher_id = t.id
  LEFT
  JOIN ( SELECT count(*) AS count_s
              , s.course_id 
           FROM student s
          GROUP 
             BY s.course_id
       ) x
    ON x.course_id = c.id                 -- relocated (added here)

该查询将返回一个有效的“计数”。

---

更简单的陈述会更容易理解。以下是我将如何获得计数:

SELECT t.id        AS teacher_id
     , COUNT(s.id) AS how_many_students_taught
  FROM teacher t
  LEFT
  JOIN course c
    ON c.id = t.course_id
  LEFT
  JOIN student s
    ON s.course_id = c.id
 GROUP
    BY t.id

---