将空值计为唯一值 [英] Counting null values as unique value

问题描述

我需要在列上计算不同的值，例如:

I need to count different values on a column, such as:

Hours
1
1
2
null
null
null

结果必须为: 3 .我的查询是:

The result must be: 3. My query is:

select count(distinct hour) from hours;

但它返回:2.我也测试了:

but it returns: 2. I tested also:

select count(*) from hours group by hour

但它返回三行:

(1) 3
(2) 2
(3) 1

如何将空值计为1值，并使用distinct避免对重复值进行计数?

How can I count null values as 1 value and use distinct to avoid count repeated values?

我正在学习高级SQL，他们希望我满足所有解决方案的这些要求:

I'm learning advanced SQL, they want me these requirements for all the solutions:

尝试最小化解决查询所需的子查询数量. 此外，不允许您使用以下构造:

Try to minimize the number of subqueries you need to solve the query. Furthermore, you are not allowed to use the following constructions:

• 在FROM或SELECT中选择.允许您有子查询(在WHERE或HAVING中的SELECT)
• 聚合函数的组合，例如COUNT(COUNT...))，SUM(COUNT...))等.
• 如果可以避免的话，UNION.
• 非标准功能(例如NVL)
• 案例

• SELECT in the FROM or SELECT. You are allowed to have subqueries (SELECT in the WHERE or HAVING)
• Combinations of aggregation functions such as COUNT (COUNT. ..)), SUM (COUNT. ..)) and the like.
• UNION if you can avoid it.
• Non-standard functions (such as NVL)
• CASE

推荐答案

select  count(distinct col1) + count(distinct case when col1 is null then 1 end)
from    YourTable

这篇关于将空值计为唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！