MySQL“GROUP BY NULL”是什么？做？ [英] What does MySQL "GROUP BY NULL" do?

问题描述

我有一些遗留代码，我在那里有一个SQL查询，我不明白。我希望这里有人能向我解释这个目的。该查询如下所示：

  select * from product_performance 
其中merchantid = 2151277和clickday> ='2011- 09-01'
 group by null; 
  

当我通过null 最后，我得到了44行。当我通过null 使用组运行它时，我得到1行 - 上一组中的第一行。这里发生了什么，使用这个分组的目的是什么？


解决方案

它将整个查询分组为一个常量值，这使得它随机选择一行来返回。您可以使用 LIMIT 1 。



获得相同的结果。请注意，此习语不是标准SQL，可能会导致如果您在其他数据库中尝试，则会出错。在MySQL中，您可以通过指定禁用此行为 ONLY FULL GROUP BY SQL模式。有关更多详细信息，请参阅文档。



OTOH， LIMIT 1 也是非标准的，但支持得更广泛，同样的效果可能是在符合SQL：2008的数据库中通过 FETCH ... ONLY 子句实现。有关详情，请参阅此。

I have some legacy code I'm maintaining and there's a SQL query in there that I don't understand. I'm hoping someone here can explain the purpose of this to me. The query looks like this:

select * from product_performance 
where merchantid = 2151277 and clickday >= '2011-09-01' 
group by null;

When I run this query without the group by null at the end, I get 44 rows. When I run it with the group by null, I get 1 row -- the first row in the previous set. What is happening here, and what is the purpose of using this grouping?

解决方案

It's grouping the entire query by a constant value, which makes it pick a row effectively at random to return. You could get the same results using LIMIT 1.

Note that this idiom is not standard SQL, and may cause errors if you try it in other databases. In MySQL, you can disable this behavior by specifying the ONLY FULL GROUP BY SQL mode. See the documentation for more details.

OTOH, LIMIT 1 is non-standard as well, but more widely supported, and the same effect may be achieved in databases compliant with SQL:2008 with the FETCH ... ONLY clause. See this for details on that.

这篇关于MySQL“GROUP BY NULL”是什么？做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！