如何根据给定外键的存在而不是该键出现的次数重写查询？

Question

在最一般的情况下，我有如下查询：

SELECT tutor_school.name, count(*), tutor_school.schoolid 

FROM tutor_school, tutor_attends, tutor_tutors_in 

WHERE 
tutor_school.schoolid = tutor_attends.schoolid and 
tutor_school.schoolid in ('1', '2', '3') and 
tutor_attends.userid=tutor_tutors_in.userid

group by tutor_school.schoolid LIMIT 0, 10

本质上，我想要：

学校名称、在该学校辅导任何科目的学生人数、Schoolid

我实际得到的内容是

学校名称，该学校学生教授的所有科目的总和，schoolid - 换句话说，如果学生 1 导师 3 门科目，学生 2 导师 5 门，那么我得到 8，而不是返回 2！

我意识到问题出在以下语句上：

tutor_attends.userid=tutor_tutors_in.userid

这不是检查远程表中给定外键的存在，而是为该键的每个实例提供结果。

我想要弄清楚的是如何绑定它以将其限制为仅给定键的存在，而不是该键出现的次数。我知道我在 SQL 课上见过类似的案例，但我不记得解决方案是什么。

Answer 1

安德鲁是正确的，这是他的解决方案的具体示例（必须对您的表格设计做出假设）：

SQL> SELECT * FROM tutor_school;

  SCHOOLID NAME
---------- --------------------
         1 School A
         2 School B
         3 School C


SQL> SELECT * FROM tutor_attends;

    USERID   SCHOOLID
---------- ----------
         1          1
         2          1
         3          2


SQL> SELECT * FROM tutor_tutors_in;

    USERID SUBJECT
---------- --------------------
         1 Math
         1 Science
         1 English
         2 English
         3 Math


SQL> SELECT tutor_school.name, COUNT(DISTINCT tutor_tutors_in.userid)
  2  FROM tutor_school, tutor_attends, tutor_tutors_in
  3  WHERE tutor_school.schoolid=tutor_attends.schoolid
  4  AND tutor_attends.userid=tutor_tutors_in.userid
  5  GROUP BY tutor_school.name
  6  /

NAME                 COUNT(DISTINCTTUTOR_TUTORS_IN.USERID)
-------------------- -------------------------------------
School A                                                 2
School B                                                 1

Answer 2

首先，您应该学习 JOIN 的 ANSI 语法。不推荐仅使用 WHERE 子句。抛开这一点，我什至可以想到一个解决方案，甚至不使用 EXISTS。

SELECT tutor_school.name, count(DISTINCT tutor_attends.userid), tutor_school.schoolid 

FROM tutor_school, tutor_attends, tutor_tutors_in 

WHERE 
tutor_school.schoolid = tutor_attends.schoolid and 
tutor_school.schoolid in ('1', '2', '3') and 
tutor_attends.userid=tutor_tutors_in.userid

group by tutor_school.schoolid LIMIT 0, 10

我对您的架构有点困惑的是，我看到了联接表，但没有看到普通的tutor 表。用一个来概念化查询可能会更容易。

Answer 3

您需要的关系运算符是 semijoin （而不是加入）。从您对需求的自然语言陈述来看，我认为使用 EXISTS 是最合适的，

例如

SELECT tutor_school.name, count(*), tutor_school.schoolid 
FROM tutor_school, tutor_tutors_in 
WHERE 
tutor_school.schoolid = tutor_attends.schoolid and 
tutor_school.schoolid in ('1', '2', '3') and
exists (
        SELECT *
          FROM tutor_attends
         WHERE tutor_attends.userid = tutor_tutors_in.userid
       )
group by tutor_school.schoolid LIMIT 0, 10