当列中的行为空时，ORACLE 显示计数为零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26404813/
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 display count to zero, when row is null in column
提问by Arno
I have a table with many of columns, I count the specific names on column and if that name not on list, I want to display it zero, but not get in to list.
我有一个包含许多列的表，我计算列上的特定名称，如果该名称不在列表中，我想将其显示为零，但不进入列表。
SELECT Names, COUNT (*)
FROM NAMESTABLE
WHERE names IN
('Jenny',
'Penny',
'George',
'James',
'Jessica',
'Monica',
'Erica')
AND
adeddate BETWEEN '2014/10/15' AND '2014/10/16'
GROUP BY names
NAMES || COUNT(*)
Jenny || 33
Penny || 4
George || 25
James || 87
so i want to Jessica, Monica, Erica as ZERO even these names are not in COLUMN
所以我想将 Jessica、Monica、Erica 设为零，即使这些名字不在列中
Names || Count(*)
Jenny || 33
Penny || 4
George || 25
James || 87
Jessica || 0
Monica || 0
Erica || 0
nvl(count(*),0)does not work
nvl(count(*),0)不起作用
回答by Mureinik
You could group your results by name and then left jointhe result to a table of your names to fill in the blanks:
您可以按名称left join对结果进行分组，然后将结果放入您姓名的表格中以填空：
SELECT rn.name, NVL(cnt, 0)
FROM (SELECT 'Jenny' AS name FROM dual
UNION ALL
SELECT 'Penny' FROM dual
UNION ALL
SELECT 'George' FROM dual
UNION ALL
SELECT 'James' FROM dual
UNION ALL
SELECT 'Jessica' FROM dual
UNION ALL
SELECT 'Monica' FROM dual
UNION ALL
SELECT 'Erica' FROM dual) rn
LEFT JOIN (SELECT name, COUNT(*) AS cnt
FROM namestable
WHERE adeddate BETWEEN '2014/10/15' AND '2014/10/16'
GROUP BY name) n ON n.name = rn.name
回答by Rimas
Then you must use other syntax:
然后你必须使用其他语法：
SELECT t.Names, COUNT(n.Names)
FROM (
SELECT 'Jenny' AS names FROM DUAL UNION ALL
SELECT 'Penny' FROM DUAL UNION ALL
SELECT 'George' FROM DUAL UNION ALL
SELECT 'James' FROM DUAL UNION ALL
SELECT 'Jessica' FROM DUAL UNION ALL
SELECT 'Monica' FROM DUAL UNION ALL
SELECT 'Erica' FROM DUAL
) t
LEFT OUTER JOIN NAMESTABLE n
ON n.names = t.names AND n.adeddate BETWEEN '2014/10/15' AND '2014/10/16'
GROUP BY t.names
回答by xQbert
This should work with a CTE..
这应该适用于 CTE ..
WITH CTE AS
(SELECT 'Jenny' Names FROM dual UNION ALL
SELECT 'Penny' FROM dual UNION ALL
SELECT 'George' FROM dual UNION ALL
SELECT 'James' FROM dual UNION ALL
SELECT 'Jessica' FROM dual UNION ALL
SELECT 'Monica' FROM dual UNION ALL
SELECT 'Erica' FROM dual)
SELECT CTE.names, coalesce(count(NT.Names),0)
FROM CTE
LEFT JOIN NAMESTABLE NT
on CTE.Names = NT.Names
and adeddate BETWEEN '2014/10/15' AND '2014/10/16'
GROUP BY CTE.Names
回答by OracleDBA
You could create an a table containing all valid names, eg valid_names table. Then join to your namestable using an outerjoin. Eg:
您可以创建一个包含所有有效名称的表，例如 valid_names 表。然后使用外连接加入您的名称表。例如：
select valid_names.name, count(1)
from namestable, valid_names
where valid_names.name=namestable.name (+)
group by valid_names.name
order by valid_names.name;
