logo标签列表

如果Count(*)为零，则返回NULL。

mysql
3

我有以下的mysql查询：

SELECT count(student_name) AS total_student,school_name FROM `student` 
LEFT JOIN school_info ON school_info.school_id=student.school_id
WHERE student.status='0'

返回值：

total_student   school_name
  0               NULL

我试图实现的目标是：如果 total_student = 0，则不显示任何值或NULL。
total_student   school_name

请问您如何做到这一点？

谢谢 :)

3个回答
7

首先，在你的查询语句底部缺少一个 GROUP BY 子句以按 school_name 进行分组：

SELECT count(student_name) AS total_student, school_name
FROM student
    LEFT JOIN school_info ON school_info.school_id = student.school_id
WHERE student.status = '0'
GROUP BY school_name

如果您想简单地不显示总学生人数为0的行，则可以使用MySQL HAVING子句：

SELECT count(student_name) AS total_student, school_name
FROM student
    LEFT JOIN school_info ON school_info.school_id = student.school_id
WHERE student.status = '0'
GROUP BY school_name
HAVING count(student_name) > 0

或者，在这种情况下，您可以将LEFT JOIN更改为INNER JOIN以实现相同的效果。

最后，如果您希望将0替换为null但仍然保留行，则可以更新获取总数的选择语句如下：

SELECT IF(COUNT(student_name) = 0, NULL, COUNT(student_name)) AS total_student, school_name
1
添加一个HAVING子句以过滤掉0行：
SELECT count(student_name) AS total_student,school_name FROM `student` 
LEFT JOIN school_info ON school_info.school_id=student.school_id
WHERE student.status='0'
HAVING total_student > 0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接