如果Count(*)为零，则返回NULL [英] Return NULL if Count(*) is zero

问题描述

我有以下mysql查询:

I have the following mysql query:

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

What I am trying to achieve is, if total_student = 0 then show no value or NULL

total_student   school_name 

你能告诉我怎么做吗?

谢谢:)

推荐答案

首先，您缺少查询底部的GROUP BY子句，用于按school_name进行分组:

First, you're missing a GROUP BY clause at the bottom of your query to 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

然后，如果您只想不显示total_student = 0的行，则可以使用MySQL HAVING子句:

Then, if you want to simply not show rows where total_student = 0 then you can use the MySQL HAVING clause:

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以完成相同的事情.

Or, you can change LEFT JOIN to INNER JOIN to accomplish the same thing in this case.

最后，如果相反，您想将0替换为null但仍然有行，则可以更新select语句，将总数取为:

Finally, if instead you want to replace 0 with null but still have rows, you could update the select statement getting the totals to:

SELECT IF(COUNT(student_name) = 0, NULL, COUNT(student_name)) AS total_student, school_name

这篇关于如果Count(*)为零，则返回NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！