如何也得到不存在的值

Question

我有一个这样的查询

select column, count(*)
from mytable
where column in ('XXX','YYY','ZZZ',....)
group by column;

但我也想得到一个行值不在表中

让我们假设'ZZZ'不存在于mytable中，我想得到:

COLUMN COUNT(*)
XXX    3
YYY    2
ZZZ    0 (or NULL)

Oracle version 10g

Thanks in advance

标记

Answer 1

一般情况下，您需要有第二个表，其中包含您希望在输出中显示其计数的所有可能列值。仅出于演示目的，我们可以使用CTE:

WITH vals AS (
SELECT 'XXX' AS val UNION ALL
SELECT 'YYY' UNION ALL
SELECT 'ZZZ'
)
SELECT t1.val, COUNT(t2.col) AS cnt
FROM vals t1
LEFT JOIN mytable t2
ON t2.col = t1.val
GROUP BY
t1.val;