sql - 带有 GROUP BY 和零条目的 MySQL COUNT

Question

我有 2 个表:

表 1. options_ethnicity 包含以下条目:

ethnicity_id ethnicity_name  
1 White  
2 Hispanic  
3 African/American  

表 2. 查询 具有以下条目:

inquiry_id ethnicity_id  
1 1  
2 1  
3 1  
4 2  
5 2  

我想生成一个表格，按种族显示查询数量。到目前为止，我的查询如下所示:

SELECT options_ethnicity.ethnicity_name, COUNT('inquiries.ethnicity_id') AS count
FROM (inquiries 
    LEFT JOIN options_ethnicity ON
    options_ethnicity.ethnicity_id = inquiries.ethnicity_id)  
GROUP BY options_ethnicity.ethnicity_id

该查询给出了正确答案，但没有包含 0 个结果的 African/American 列。

White 3  
Hispanic 2

如果我用 RIGHT JOIN 替换 LEFT JOIN，我会得到所有 3 个种族名称，但非洲/美国人的计数是错误的。

White 3  
Hispanic 2  
African/American 1

如有任何帮助，我们将不胜感激。

这是对这篇文章的更新，其中包含一个似乎有效的查询:

SELECT 
    options_ethnicity.ethnicity_name, 
    COALESCE(COUNT(inquiries.ethnicity_id), 0) AS count 
FROM options_ethnicity LEFT JOIN inquiries ON inquiries.ethnicity_id = options_ethnicity.ethnicity_id 
GROUP BY options_ethnicity.ethnicity_id 

UNION ALL

SELECT 
    'NULL Placeholder' AS ethnicity_name, 
    COUNT(inquiries.inquiry_id) AS count 
FROM inquiries 
WHERE inquiries.ethnicity_id IS NULL 

Answer 1

因为您使用的是 LEFT JOIN，所以对 LEFT JOIN 中定义的表的引用可以为空。这意味着您需要将此 NULL 值转换为零(在本例中):

   SELECT oe.ethnicity_name, 
          COALESCE(COUNT(i.ethnicity_id), 0) AS count
     FROM OPTIONS_ETHNICITY oe
LEFT JOIN INQUIRIES i ON i.ethnicity_id = oe.ethnicity_id
 GROUP BY oe.ethnicity_id

此示例使用 COALESCE ，一种处理 NULL 值的 ANSI 标准方法。它将返回第一个非空值，但如果找不到，它将返回空值。 IFNULL是 MySQL 上的有效替代方案，但它不能移植到其他数据库，而 COALESCE 是。

---

In the real database table, there are some entries in the inquires table where the ethnicity_id is NULL, i.e. the ethnicity was not recorded. Any idea on how to get these null values to be counted so that they can be shown?

我想我理解您面临的问题:

   SELECT oe.ethnicity_name, 
          COALESCE(COUNT(i.ethnicity_id), 0) AS count
     FROM (SELECT t.ethnicity_name,
                  t.ethnicity_id
             FROM OPTIONS_ETHNICITY t
           UNION ALL
           SELECT 'NULL placeholder' AS ethnicity_name,
                  NULL AS ethnicity_id) oe
LEFT JOIN INQUIRIES i ON i.ethnicity_id = oe.ethnicity_id
 GROUP BY oe.ethnicity_id

这将拾取所有 NULL ethncity_id 实例，但它将计数归因于“NULL 占位符”组。即:

ethnicity_name   |  COUNT
------------------------
White            |  3  
Hispanic         |  2
NULL placeholder |  ?