MySQL - 计数没有返回我正在寻找的值

Question

我对 SQL 还很陌生，我还没有完全理解我的代码中的问题来自哪里。下面的代码主要来 self 的工作，所以我不是从头开始编写的。该代码收集了一堆不同的信息并基于它进行过滤。如果您查看代码，您会发现一个学生有许多与其相关的obstudents。代码的第一个版本返回所有拥有 Observations_student 且 Observation_id = 2567 的学生的信息。这似乎可以与以下代码正常工作:

SELECT DISTINCT
SUBSTRING(s.osis_id,INSTR(s.osis_id,'-')+1) AS osid,
s.id AS student_id,
CONCAT(s.last_name, ' ',s.first_name) AS sname
FROM students s

# course info
INNER JOIN 
(
    SELECT c.id AS cid,
    c.description AS cname,
    cs.date_end,
    cs.student_id,
    gl.description AS grade,
    c.gradelevel_id
    FROM courses_students cs
    INNER JOIN courses c ON c.id = cs.course_id
    INNER JOIN gradelevels gl ON gl.id = c.gradelevel_id
    WHERE
    IFNULL(cs.date_end, NOW()) >= NOW()
    AND IFNULL(c.date_end, NOW()) >= NOW()
    AND c.school_id = 1509
    AND c.subject_id = 24
) AS cs ON cs.student_id = s.id

# RTI flag info
INNER JOIN 
(
    SELECT os.id,
    os.student_id
    FROM observations o
    INNER JOIN observations_students os ON os.observation_id = 2567
    WHERE
    o.school_id = 1509
) AS os ON os.student_id = s.id

LEFT JOIN schools_students ss ON ss.student_id = s.id
WHERE s.active = 1
AND ss.school_id = 1509
AND IFNULL(ss.date_end,NOW()) >= NOW()
AND cs.gradelevel_id BETWEEN 10 AND 16

此后我想要做的是对于每个拥有 2567 个观察值的学生，我想找到该学生拥有的 2009 年观察值的数量。为此，我添加另一个 LEFT JOIN，完成的代码如下所示:

SELECT DISTINCT
SUBSTRING(s.osis_id,INSTR(s.osis_id,'-')+1) AS osid,
s.id AS student_id,
CONCAT(s.last_name, ' ',s.first_name) AS sname,
COUNT(fdos.id) AS fd_count
FROM students s

# course info
INNER JOIN 
(
    SELECT c.id AS cid,
    c.description AS cname,
    cs.date_end,
    cs.student_id,
    gl.description AS grade,
    c.gradelevel_id
    FROM courses_students cs
    INNER JOIN courses c ON c.id = cs.course_id
    INNER JOIN gradelevels gl ON gl.id = c.gradelevel_id
    WHERE
    IFNULL(cs.date_end, NOW()) >= NOW()
    AND IFNULL(c.date_end, NOW()) >= NOW()
    AND c.school_id = 1509
    AND c.subject_id = 24
) AS cs ON cs.student_id = s.id

# RTI flag info
INNER JOIN 
(
    SELECT os.id,
    os.student_id
    FROM observations o
    INNER JOIN observations_students os ON os.observation_id = 2567
    WHERE
    o.school_id = 1509
) AS os ON os.student_id = s.id

LEFT JOIN
(
    SELECT fdos.id,
    fdos.student_id
    FROM observations o
    INNER JOIN observations_students fdos ON fdos.observation_id = 2009
    WHERE
    o.school_id = 1509
) AS fdos ON fdos.student_id = s.id

LEFT JOIN schools_students ss ON ss.student_id = s.id
WHERE s.active = 1
AND ss.school_id = 1509
AND IFNULL(ss.date_end,NOW()) >= NOW()
AND cs.gradelevel_id BETWEEN 10 AND 16

如果我将“COUNT(fdos.id) AS fd_count”更改为“fdos.id AS fdosid”，我将返回正确的条目数。但是，从 COUNT 返回的数字不是同一个数字，并且不正确。任何人都可以充分理解这里发生的事情来解释我做错了什么吗？

感谢您的宝贵时间。

Answer 1

我敢打赌您正在使用 MySQL。

如果您使用以下任何内容:

• GROUP BY 子句；
• HAVING 子句；
• 聚合函数，count() 是

那么您的查询将被聚合。

这意味着，数据将按GROUP BY子句中指定的字段进行分组，这些字段应按原样保留在选择列表中以及其他位置查询。所有其他字段都应该是聚合函数的参数，否则数据库不知道它应该返回与您的组匹配的集合中的哪个值。

对于按照您的方式构造的查询，所有主要数据库都会报错，因为一堆字段没有 GROUP BY 子句:s.osis_id， s.id、s.last_name 和 s.first_name。 MySQL 不会。相反，它会隐式对数据进行分组。我不知道分组标准是什么，我也不想知道，因为这种行为容易出错且不可靠。

相反，您的查询应该被重写。最简单的方法是:

• 使用现有查询而不使用 count() 函数，即获取 fdos.id 列表；
• 将整个查询用作另一个子查询，省略 DISTINCT 子句；
• 清点学生人数。

类似这样的事情:

SELECT osid, student_id, sname, count(fdos_id) AS fd_count
  FROM (
    SELECT
        substring(s.osis_id,instr(s.osis_id,'-')+1) AS osid,
        s.id AS student_id,
        concat(s.last_name, ' ',s.first_name) AS sname,
        fdos.id AS fdos_id
      FROM students s
       ...
  ) AS src
 GROUP BY osid, student_id, sname
 ORDER BY osid, student_id, sname;