Oracle SELECT COUNT(*) ... GROUP BY ... 不返回计数为零的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3974501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SELECT COUNT(*) … GROUP BY … not returning rows where the count is zero
提问by ErAB
I tried the solution given in "mysql SELECT COUNT(*) … GROUP BY … not returning rows where the count is zero" but it didn't work for me.
我尝试了“mysql SELECT COUNT(*) ... GROUP BY ... 不返回计数为零的行”中给出的解决方案，但它对我不起作用。
Here is SQL ::
这是 SQL ::
SELECT empid, SUM (total_records) total_records
FROM (SELECT ID,
(CASE WHEN ID LIKE '2%' THEN '2____'
WHEN ID LIKE '3%' THEN '3____'
WHEN ID LIKE '99%' THEN '99____'
END) empid,
total_records
FROM tr
where ( id like '2%' or id like '3%' or id like '99%'))
GROUP BY empid;
When the particular value in empid SUM is ZERO, that row isn't shown at the output. This shows we have no data (or sum) for that particular empid. But is it possible to have '0' for that particular value to be displayed in the result set ?
当 empid SUM 中的特定值为零时，该行不会显示在输出中。这表明我们没有该特定 empid 的数据（或总和）。但是是否有可能在结果集中显示该特定值的“0”？
回答by Gary Myers
Adding detail to both ivanpro and Michael Goldshteyn's answers, here is a demo with a dummy 'table' with the three desired empid values outer joined against the original query
为 ivanpro 和 Michael Goldshteyn 的答案添加详细信息，这是一个带有虚拟“表”的演示，其中三个所需的 empid 值外部连接到原始查询
SELECT a.empid, nvl(SUM (total_records),0) total_records
FROM (select '2____' empid from dual union select '3____' from dual
union select '99____' from dual) a
left outer join
(SELECT ID,
(CASE WHEN ID LIKE '2%' THEN '2____'
WHEN ID LIKE '3%' THEN '3____'
WHEN ID LIKE '99%' THEN '99____'
END) empid,
total_records
FROM tr
where ( id like '2%' or id like '3%' or id like '99%')) b
on a.empid = b.empid
GROUP BY a.empid;
回答by Michael Goldshteyn
Not with this query, you would need to union its result set with another query that finds the missing values.
不是使用此查询，您需要将其结果集与另一个查找缺失值的查询合并。
