mysql - 即使没有 SUM 结果，如何显示 mySQL 行？

Question

我正在尝试显示来自 mySQL 的报告。这是我当前的查询:

SELECT *,
       Sum(CASE 
             WHEN alerts_data_status = 'goal' THEN 1 
             ELSE 0 
           END) AS goal, 
       Sum(CASE 
             WHEN alerts_data_status = 'delivered' THEN 1 
             ELSE 0 
           END) AS delivered, 
       Sum(CASE 
             WHEN alerts_data_status = 'closed' THEN 1 
             ELSE 0 
           END) AS closed
FROM   alerts_data 
WHERE  alerts_data.company_id = 1
GROUP  BY alerts_data.alerts_data_id 

问题是，如果 alerts_data.id 有 0 个目标、0 个交付、0 个关闭，则它不会显示在结果中。该查询仅显示至少有 1 个目标或 1 个已交付或 1 个已关闭的 alerts_data.id。

我怎样才能实现这个目标？

输出示例

company ---- id --- goal --- delivered --- closed
1 ---- 32 --- 1 ------ 4 ----- 10
1 ---- 11 --- 0 ------ 1 ----- 1

谢谢

Answer 1

我认为您遇到的问题是表中没有该公司的行。使用不带 GROUP BY 的聚合查询:

SELECT 1 as company_id,
       COALESCE(SUM(alerts_data_status = 'goal'), 0) AS goal, 
       COALESCE(SUM(alerts_data_status = 'delivered'), 0) AS delivered, 
       COALESCE(SUM(alerts_data_status = 'closed'), 0) AS closed
FROM alerts_data ad
WHERE ad.company_id = 1;

这不是GROUP BY，这保证返回一行——即使WHERE子句过滤掉所有行。 GROUP BY 每组返回一行，因此如果所有行都被过滤掉，那么结果集中就没有组，也没有行。

如果您想支持多个公司 ID，您可以使用LEFT JOIN:

SELECT company_id,
       COALESCE(SUM(alerts_data_status = 'goal'), 0) AS goal, 
       COALESCE(SUM(alerts_data_status = 'delivered'), 0) AS delivered, 
       COALESCE(SUM(alerts_data_status = 'closed'), 0) AS closed
FROM (SELECT 1 as company_id UNION ALL
      SELECT 2 as company_id
     ) c LEFT JOIN
     alerts_data ad
     USING (company_id)
GROUP BY company_id;

LEFT JOIN 保证每个公司都有行，因此每个公司都将出现在结果集中。

您也可以将其表述为:

SELECT 1 as company_id,
       COALESCE(SUM(alerts_data_status = 'goal'), 0) AS goal, 
       COALESCE(SUM(alerts_data_status = 'delivered'), 0) AS delivered, 
       COALESCE(SUM(alerts_data_status = 'closed'), 0) AS closed
FROM alerts_data ad
WHERE ad.company_id = 1;

这不是GROUP BY，这保证返回一行——即使WHERE子句过滤掉所有行。 GROUP BY 每组返回一行，因此如果所有行都被过滤掉，那么结果集中就没有组，也没有行。

如果您想支持多个公司 ID，您可以使用LEFT JOIN:

SELECT c.company_id,
       COALESCE(SUM(ad.alerts_data_status = 'goal'), 0) AS goal, 
       COALESCE(SUM(ad.alerts_data_status = 'delivered'), 0) AS delivered, 
       COALESCE(SUM(ad.alerts_data_status = 'closed'), 0) AS closed
FROM companies c LEFT JOIN
     alerts_data ad
     on c.company_id = ad.company_id
WHERE c.company_id IN (1)   -- or a longer list
GROUP BY c.company_id;