为什么此查询返回带有空值的行 [英] why does this query return a row with nulls

问题描述

比较

SELECT distinct u_id,timestamp as time
FROM my_table;

和

SELECT distinct u_id,max(timestamp) as time
FROM my_table;

当我的表根本没有行时(或者如果我添加了一个不匹配行的where子句):

When my table has no rows at all (or if I add a where clause that matches no rows):

前者返回一个空结果集(这是我所期望的) 而后者则返回一行，两个字段的值都为null.

The former returns an empty results set (which is what I expect) while the later returns a single row that has null as the value for both its fields.

有人可以向我解释为什么第二个为什么这样做吗?

Can someone please explain to me why does the second one acts as it does?

推荐答案

MySQL文档说

如果没有匹配的行，则MAX()返回NULL.

MAX() returns NULL if there were no matching rows.

如果没有数据，那么它只会将两个值都返回为NULL.

And if you have no data then it just returns both values as NULL.

如果您希望第二个查询也返回空结果集，则必须使用可与聚合函数一起使用的HAVING子句过滤掉NULL值:

If you want the second query return the empty resultset too, then you must filter out the NULL values for example with HAVING clause that you can use with aggregate functions:

SELECT DISTINCT u_id, MAX(timestamp) as time FROM my_table GROUP BY u_id HAVING time IS NOT NULL;

这篇关于为什么此查询返回带有空值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！