MySQL COUNT，具有GROUP BY和零项

admin

MySQL COUNT，具有GROUP BY和零项

sql

我有2张桌子：

表1. options_ethnicity具有以下条目：

ethnicity_id ethnicity_name  
1 White  
2 Hispanic  
3 African/American

表2. inquiries带有以下条目：

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。

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

阅读 143

2021-06-07

共1个答案

admin

由于您使用的是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标准方法）。它将返回第一个非null值，但是如果找不到，则将返回null。
IFNULL在MySQL上是有效的替代方法，但是当COALESCE可以使用时，它不能移植到其他数据库。

在实际数据库表中，查询表中有一些条目，其中ethnicity_id为NULL，即未记录种族。关于如何获取这些空值以进行显示的任何想法吗？

我想我了解您面临的问题：

   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占位符”组。IE：

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

2021-06-07