oracle COUNT 返回 NULL,应返回 0
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/19201089/
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
提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:59:04 来源:igfitidea点击:
COUNT returning NULL, should return 0
提问by sonakshi sinha
I have a simple query for the table:
我对表有一个简单的查询:
Person id Organization id employee_nam age busines_group_id
123 Zuyo 10 John 30 81
2457 Zuyo 10 Geet 69 81
56 Ghiya 12 paul 20 81
frei 13 81
SELECT
COUNT(DISTINCT ped.person_id)
FROM
per_emp_detail ped
WHERE
ped.business_group_id = 81
AND
ped.id = NVL(p_org_id, ped.organization_id);
SELECT
NVL(COUNT(DISTINCT ped.person_id), 0)
FROM
per_emp_detail ped
WHERE
ped.business_group_id = 81
AND
ped.id = NVL(p_org_id, ped.organization_id);
p_org_id
is the parameter which I am passing which can be 10, 12, or 13.
COUNT
returns 2
for id 10. 1
for id 12. but is returning NULL
for id 13.
I want 0
to be returned in this case.
p_org_id
是我传递的参数,它可以是 10、12 或 13。
COUNT
返回2
id 10。返回1
id 12。但返回NULL
id 13。我想0
在这种情况下返回。
NVL
and CASE
are also not working.
NVL
并且CASE
也不工作。
回答by electrobabe
try MAX:
尝试最大:
nvl(max(count(DISTINCT ped.person_id)),0)