gpt4 book ai didi

mysql - 如果没有结果,则在 COUNT() 操作期间不返回记录

转载 作者:行者123 更新时间:2023-11-29 02:40:59 27 4
gpt4 key购买 nike

我想知道有多少学生注册了一个类(class)。如果有记录,上述方法有效。

但是,如果没有结果,则返回单个“记录”,其中所有字段均为 NULL,但返回零的 students_count 除外

+---+------+------+----------------+----------------+
| | id | name | professor_name | students_count |
+---+------+------+----------------+----------------+
| 1 | null | null | null | 0 |
+---+------+------+----------------+----------------+

我希望不返回任何记录。

如果记录返回,它看起来像这样。

+---+----+-------------+----------------+----------------+
| | id | name | professor_name | students_count |
+---+----+-------------+----------------+----------------+
| 1 | 1 | Science 101 | Atkins | 16 |
+---+----+-------------+----------------+----------------+

我尝试了 LEFT JOIN 的变体和 IFNULL(COUNT(DISTINCT students.id), null) AS students_count 的组合

但我似乎无法让它工作。

有什么帮助吗?

SELECT  classes.*,
professors.name AS professor_name,
COUNT(DISTINCT students.id) AS students_count
FROM classes
INNER JOIN professors ON classes.professor_id = professors.id
LEFT JOIN students ON classes.id = students.class_id AND classes.class_id IS NOT NULL
WHERE classes.class_id = 3

使用下面的通用数据,class_id 为 3,结果应该没有记录。但是返回的是计数为0的null记录。

+---+------+------+----------------+----------------+
| | id | name | professor_name | students_count |
+---+------+------+----------------+----------------+
| 1 | null | null | null | 0 |
+---+------+------+----------------+----------------+

使用class_id为1会返回:

+---+----+-------------+----------------+----------------+
| | id | name | professor_name | students_count |
+---+----+-------------+----------------+----------------+
| 1 | 1 | Science 101 | Atkins | 4 |
+---+----+-------------+----------------+----------------+

通用数据

+---+-------------+---------------+
| | name | professors_id |
+---+-------------+---------------+
| 1 | Science 101 | 1 |
+---+-------------+---------------+
| 2 | Math | 2 |
+---+-------------+---------------+
| 3 | English | 3 |
+---+-------------+---------------+

教授

+----+--------+
| id | name |
+----+--------+
| 1 | Atkins |
+----+--------+
| 2 | Button |
+----+--------+
| 3 | Castor |
+----+--------+

学生

+----+-------+------------+
| id | name | classes_id |
+----+-------+------------+
| 1 | Adam | 1 |
+----+-------+------------+
| 2 | Beth | 1 |
+----+-------+------------+
| 3 | Chris | 1 |
+----+-------+------------+
| 4 | David | 1 |
+----+-------+------------+
| 5 | Erma | 2 |
+----+-------+------------+

最佳答案

您可以尝试使用 INNER JOIN 而不是 OUTER JOIN,因为 LEFT JOIN 将基于 classes 表格。

SELECT  classes.*,
professors.name AS professor_name,
COUNT(DISTINCT students.id) AS students_count
FROM classes
INNER JOIN professors ON classes.professor_id = professors.id
INNER JOIN students ON classes.id = students.class_id
WHERE classes.class_id = 3

sqlfiddle

编辑

HAVING 子句用于聚合函数条件。但是你使用 HAVING classes.class_id IS NOT NULL 可以移动到 where

SELECT  classes.*,
professors.name AS professor_name,
COUNT(DISTINCT students.id) AS students_count
FROM classes
INNER JOIN professors ON classes.professor_id = professors.id
LEFT JOIN students ON classes.id = students.class_id
WHERE classes.class_id = 3 AND classes.class_id IS NOT NULL

关于mysql - 如果没有结果,则在 COUNT() 操作期间不返回记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52239845/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com