tsql - 分组但包括 "missing"值

Question

假设我有以下内容。

select
  case
    when fcompany = 'Acme' then 'Red'
    when fcompany = 'Acme Rockets' then 'Blue'
    else 'Green'
  end
    Color
,sum(fann_sales)
FROM
  slcdpm
group by
  case
    when fcompany = 'Acme' then 'Red'
    when fcompany = 'Acme Rockets' then 'Blue'
    else 'Green'
  end

假设它通常只返回两种颜色。提取所有三种颜色并包含 0 作为缺失值的最佳方法是什么？

联合所有？

Answer 1

将 GROUP 移动到具有更多列的条件 SUM 中？

select
  sum(CASE WHEN fcompany = 'Acme'
                     THEN fann_sales ELSE 0 END) AS redsales,
  sum(CASE WHEN fcompany = 'Acme Rockets'
                     THEN fann_sales ELSE 0 END) AS bluesales
  sum(CASE WHEN fcompany NOT IN ('Acme Rockets', 'Acme')
                     THEN fann_sales ELSE 0 END) AS greensales
FROM
  slcdpm

为此，请绕过 table 。 UNION ALL 或子查询方法(在其他答案中)将每个子句触及一次表 = 稍慢。