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
3
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
0
To count all the rows with null date this should work:
要计算所有具有空日期的行,这应该是可行的:
SELECT COUNT(*) FROM table WHERE date IS NULL;
#3
0
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
0
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
3
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
0
To count all the rows with null date this should work:
要计算所有具有空日期的行,这应该是可行的:
SELECT COUNT(*) FROM table WHERE date IS NULL;
#3
0
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
0
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