MYSQL:COUNT与GROUP BY，LEFT JOIN和WHERE子句不返回零值 [英] MYSQL: COUNT with GROUP BY, LEFT JOIN and WHERE clause doesn't return zero values

问题描述

我确定这是一个非常简单的答案，但是我似乎找不到(不确定要搜索的内容！).标准计数/按查询分组可能看起来像这样:

I'm sure this has a very simple answer, but I can't seem to find it (not sure what to search on!). A standard count / group by query may look like this:

SELECT COUNT(`t2`.`name`) 
FROM `table_1` `t1` 
    LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` 
WHERE `t1`.`another_column` = 123 

，这将按预期工作，如果未找到任何行，则返回0.但是:

and this works as expected, returning 0 if no rows are found. However:

SELECT COUNT(`t2`.`name`) 
FROM `table_1` `t1` 
    LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` 
WHERE `t1`.`another_column` = 123 
GROUP BY `t1`.`any_col` 

仅在table_1中至少有一行时有效，而在有零行时无法返回空结果集，则失败.我真的很想返回0！有人启发我吗?如果您在伦敦，可以提供啤酒作为交换;-)

only works if there is at least one row in table_1 and fails miserably returning an empty result set if there are zero rows. I would really like this to return 0! Anyone enlighten me on this? Beer can be provided in exchange if you are in London ;-)

推荐答案

它返回零行的原因是您正在对table_1中的值进行分组.由于table_1中没有值，因此没有要返回的行.换句话说，如果您从GROUP BY中返回查询中的t1.any_col，如下所示:

The reason it returns zero rows is that you are grouping on a value in table_1. SInce there are no values in table_1, there are no rows to return. Said another way, if you returned t1.any_col in your query from the GROUP BY like so:

SELECT `t1`.`any_col`, COUNT(`t2`.`name`) 
FROM `table_1` `t1` 
    LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` 
WHERE `t1`.`another_column` = 123 
GROUP BY `t1`.`any_col` 

在没有行的情况下，t1.any_col将显示什么?实现所需目标的唯一方法是将结果与另一个检查table_1中没有行的查询合并.在此示例中，我仅使用INFORMATION_SCHEMA视图来查询某些内容.

What would display for t1.any_col when there were no rows? The only way to achieve what you want is to union your results with another query that checks for no rows in table_1. In this example, I'm using the INFORMATION_SCHEMA view simply to have something against which I can query.

SELECT COUNT(`t2`.`name`) 
FROM `table_1` `t1` 
    LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` 
WHERE `t1`.`another_column` = 123 
GROUP BY `t1`.`any_col` 
UNION ALL
SELECT 0
FROM INFORMATION_SCHEMA.TABLES
Where Not Exists( Select 1 From `table_1` )
LIMIT 1

这篇关于MYSQL:COUNT与GROUP BY，LEFT JOIN和WHERE子句不返回零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！