子查询时返回null

I try to do the the following query with mysql (Should add more conditions, but simplified it for the question, so the sub-query sometimes return null and sometimes return value, this is just for making the query shorter for the question):

我尝试使用mysql进行如下查询(应该增加更多的条件，但对问题进行了简化，因此子查询有时返回null，有时返回值，这只是为了使查询更简短):

SELECT COUNT(*)
FROM table
WHERE date = (SELECT MAX(date) FROM table)

My problem is that if the sub-query return null, my result will be 0, which is not the desired result. Since I can't do IS instead of =, I'm wondering if there is a simple solution.

我的问题是，如果子查询返回null，那么结果将为0，这不是期望的结果。因为我不能代替=，所以我想知道是否有一个简单的解决方案。

4 个解决方案

#1

MySQL provides a NULL safe equality comparison <=> (spaceship) operator.

MySQL提供了一个空安全相等比较<=>(太空船)操作符。

I suspect that if you replace the = equality comparison operator with the NULL safe equality comparison operator, the query will return the results it looks like you are after.

我怀疑，如果将=相等比较运算符替换为NULL安全相等比较运算符，查询将返回结果，它看起来就像您在后面。

This expression:

这个表达式:

  a <=> b

is basically shorthand equivalent for:

基本相当于:

  a = b OR ( a IS NULL AND b IS NULL )

#2

To count all the rows with null date this should work:

要计算所有具有空日期的行，这应该是可行的:

SELECT COUNT(*) FROM table WHERE date IS NULL;

#3

This should not be a concern.

这不应该是一个问题。

The subquery only returns NULL if there are no rows or if date is NULL in all rows.

子查询仅在所有行中没有行或日期为空时返回NULL。

The query will return 0 in this case. That makes sense to me. What would you want it to return?

在这种情况下，查询将返回0。这对我来说很有意义。你希望它返回什么?

#4

You can make a union of both results and calculate the max:

你可以把两个结果结合起来，计算出最大值:

SELECT MAX(datecount) FROM
(SELECT COUNT(*) AS datecount FROM table WHERE date IS NULL 
UNION ALL
SELECT COUNT(*) AS datecount FROM table WHERE date = (SELECT MAX(date) FROM table)) AS derivedtable

#1