为“COUNT”/“GROUP BY”获取空结果MySQL查询 [英] Getting Empty Results For 'COUNT'/'GROUP BY' MySQL Query

问题描述

我得到类似的问题，因为这里发布的问题：如何在使用select，group by和count时获取非空结果集？

然而，给出的解决方案较慢，由回答者提及。我只是想知道是否有任何替代解决方案，而不影响性能？

另外，我不明白为什么像这样的查询：

  SELECT 
`a`，`b`，COUNT（*）as`c` 
 FROM`mytable` 
 WHERE`status `= 1 
 GROUP BY`a`，`b` 
  

将返回空结果只有没有'GROUP BY'部分，它显示预期结果为0作为计数值？这是可能的mysql错误吗？

我也发现了一个关于mysql bug门户的讨论，有类似的问题，这里： http://bugs.mysql.com/bug.php?id=26087

但是这个讨论还不够成熟，不能得到合适的答案。

我的问题：

• 这可能是一个可能的mysql错误吗？


• 可能有一个比给定的更好的替代解决方案开始）？如果是，如何？

解决方案

这不是MySQL错误。

聚合查询将返回遇到的每个组一行。如果没有组，则有一个组 - 整个表。我承认这有点棘手，因为仍然有一个组，即使在表中没有行。

使用组，每个组有一行。如果组中没有行，则不会显示该组。在你的case，查询：

  SELECT`a`，`b`，COUNT（*）as`c` 
 FROM`mytable` 
 WHERE`status` = 1 
  

将返回一行，两个 NULL s后面跟 0 。

相同的查询：

  GROUP BY`a`，`b` 
  pre> 
 
 
将不返回任何行，因为没有行来形成组。
 
I am getting similar problem as the issue posted here: How can I get a non empty result set when using select, group by and count?

However, the solution given is slower, mentioned by the answerer. I was just wondering if there any alternative solution without compromising performance?

Also, I don't understand why a query like:
SELECT
`a`, `b`, COUNT(*) as `c`
FROM `mytable` 
WHERE `status` = 1
GROUP BY `a`,`b`
will return empty result where only without the 'GROUP BY' part it shows expected result with 0 as count value? Is this can be a possible mysql bug?

I have also found a discussion on mysql bug portal, with similar issue, here: http://bugs.mysql.com/bug.php?id=26087

But this discussion isn't matured enough to get a suitable answer, I guess.

My Questions:


Is this could be a possible mysql bug?
Is is possible to have a better alternative solution than the given one(the link at the beginning)? If yes, how?

解决方案
This is not a MySQL bug.

An aggregation query will return one row per group that is encountered.  Without a group by, there is one group -- the entire table.  I admit this is a bit tricky, because there is still one group, even when there are no rows in the table.

With a group by, there is one row per group.  If there are no rows in a group, then the group does not appear.  In your case, the query:
SELECT `a`, `b`, COUNT(*) as `c`
FROM `mytable` 
WHERE `status` = 1
Will return one row, with two NULLs followed by 0.

The same query with:
GROUP BY `a`,`b`
will return no rows because there are no rows to form groups.

                        
这篇关于为“COUNT”/“GROUP BY”获取空结果MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！