sql - 如果搜索未找到值，则在 sql 中添加硬编码行

Question

所以我有一个像这样的表:

我有选择....

  SELECT colors,
  COUNT (Num1) AS number1,
 COUNT (Num2) AS number2,
  COUNT (Num3) AS number3,
  COUNT (Num4) AS number4
  From table
  Group By colors

结果将是:

我的问题是:如果没有某些显示的颜色，我如何在结果的末尾添加一行包含该颜色的计数(0 0 0 0)？注意:结果中必须显示所有颜色，但并非所有颜色都必须写在表格中

Answer 1

您可以使用 LEFT JOIN 来返回所有可能颜色的子查询:

SELECT s.color, 
      COUNT (t.Num1) AS number1,
      COUNT (t.Num2) AS number2,
      COUNT (t.Num3) AS number3,
      COUNT (t.Num4) AS number4
FROM (-- subquery or table of all possible colors
) s
LEFT JOIN table t
  ON s.color = t.color
GROUP BY s.color;

Rextester Demo

编辑:

或者使用 UNION ALL:

WITH cte AS (
    SELECT t.color, 
          COUNT (t.Num1) AS number1,
          COUNT (t.Num2) AS number2,
          COUNT (t.Num3) AS number3,
          COUNT (t.Num4) AS number4
    FROM table t
    GROUP BY t.color
)
SELECT * 
FROM cte
UNION ALL
SELECT color, 0, 0, 0, 0
FROM (--table or subquery) s
WHERE NOT EXISTS (SELECT *
                  FROM cte c
                  WHERE c.color = s.color);

Rextester Demo 2