SQL 将空值计算为唯一值

Counting null values as unique value

提问by David

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

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

Hours
1
1
2
null
null
null

The result must be: 3. My query is:

结果必须是：3。我的查询是：

select count(distinct hour) from hours;

but it returns: 2. I tested also:

但它返回：2.我也测试过：

select count(*) from hours group by hour

but it returns three rows:

但它返回三行：

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

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

如何将空值计为 1 值并使用 distinct 避免计数重复值？

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

我正在学习高级 SQL，他们希望我对所有解决方案都有这些要求：

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

• 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 中的 FROM 或 SELECT。您可以有子查询（在 WHERE 或 HAVING 中选择）
• COUNT(COUNT...))、SUM(COUNT...))等聚合函数的组合。
• UNION 如果可以避免的话。
• 非标准功能（如NVL）
• 案件

回答by Andomar

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

回答by David

if hour is a number, then if it can only be an integer:

如果小时是一个数字，那么如果它只能是一个整数：

select count(distinct coalesce(hour, 0.1)) cnt from test;

otherwise if it can be any floating point, change NULL to a char string.

否则，如果它可以是任何浮点数，请将 NULL 更改为字符字符串。

eg

例如

select count(distinct coalesce(to_char(hour), 'a')) cnt from test;

回答by Egor Skriptunoff

select 
   count(0) 
from
  (
      select distinct hour from hours
  )

SqlFiddle

SqlFiddle

回答by ypercube??

SELECT
      ( SELECT COUNT(DISTINCT hour)
        FROM hours
      )
    + CASE WHEN EXISTS
           ( SELECT *
             FROM hours
             WHERE hour IS NULL
           )
        THEN 1 
        ELSE 0
      END
   AS result
FROM dual ;

回答by Andres

maybe

也许

    select count(distinct hour||' ') from hours;

will do?

会做？

回答by Giampaolo

select count(distinct nvl(hour,0)) from hours;

回答by arana

Answer by Andres is the one that meets the requirements perfectly and without using any function at all apart from COUNT:

安德烈斯的回答是完美满足要求的，除了COUNT：

select count(distinct hour||' ') from hours;

i was looking for same thing for another purpose ( I could use anything at all ) but it did not seem correct or efficient to me until I saw this one, thank you Andres, such a simple solution yet a powerful one.

我正在为另一个目的寻找同样的东西（我可以使用任何东西），但在我看到这个之前，它对我来说似乎并不正确或有效，谢谢安德烈斯，这样一个简单但功能强大的解决方案。

回答by Ben

I'd say your requirements are pretty bizarre, given that you're almost certain to get a more efficient query simply using NVL(), COALESCE()or CASE. However, I managed to get the right result (and cope with the presence or absence of NULLvalues) using only subqueries. I've not managed to do this without using a subquery in the FROMclause yet.

我想说您的要求非常奇怪，因为您几乎可以肯定，只需使用NVL(),COALESCE()或即可获得更有效的查询CASE。但是，我NULL仅使用子查询就设法获得了正确的结果（并处理值的存在与否）。我还没有在FROM子句中使用子查询来做到这一点。

SQL Fiddle

SQL小提琴

Query 1:

查询 1：

SELECT nnh.not_null_hours + nh.null_hours
FROM (
  SELECT COUNT(DISTINCT t.hour) not_null_hours
  FROM example_table t
) nnh
CROSS JOIN (
  SELECT 1 null_hours
  FROM dual
  WHERE EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
  UNION ALL
  SELECT 0 null_hours
  FROM dual
  WHERE NOT EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
) nh

Results:

结果：

| NNH.NOT_NULL_HOURS+NH.NULL_HOURS |
------------------------------------
|                                3 |

This is going to a lot of effort to cope with the requirements. A much simpler option is to use NVL, and then one of two simple choices... either:

这将需要付出很多努力来满足要求。一个更简单的选择是使用NVL, 然后是两个简单的选择之一......要么：

1. Use TO_CHARto convert the non-NULL values to the datatype VARCHAR2 and NVLto convert NULLvalues to the VARCHAR2 'NULL'or
2. Just use NVLwith a magic number that you know will never be present in the result set (i.e. because of constraints on the table).

1. 使用TO_CHAR的非NULL值转换为数据类型VARCHAR2和NVL转换NULL值的VARCHAR2'NULL'或
2. 只需使用NVL一个您知道永远不会出现在结果集中的幻数（即，由于表上的约束）。

Query 1:

查询 1：

SELECT 
  COUNT(DISTINCT NVL(TO_CHAR(hour), 'NULL')) using_to_char_null
, COUNT(DISTINCT NVL(hour, -1)) using_magic_number
FROM example_table

Results:

结果：

| USING_TO_CHAR_NULL | USING_MAGIC_NUMBER |
-------------------------------------------
|                  3 |                  3 |

回答by Ben

The closest I could get fitting the criteria specified is this: (SQL Fiddle)

我能得到的最接近指定的标准是这样的：（SQL Fiddle）

Query 1:

查询 1：

SELECT COUNT(*)
FROM example_table t1
WHERE t1.ROWID IN (
  SELECT MAX(t2.ROWID)
  FROM example_table t2
  GROUP BY t2.hour
)

Results:

结果：

| COUNT(*) |
------------
|        3 |

Not sure if the ROWIDpseudocolumn is allowed, given the other restrictions, but it works and gracefully handles NULLvalues. I don't think ROWID exists outside of Oracle, so likely this is going against the spirit of the question, but it fits the criteria listedat least.

ROWID鉴于其他限制，不确定是否允许使用伪列，但它可以正常工作并优雅地处理NULL值。我认为 ROWID 不存在于 Oracle 之外，因此这很可能违背了问题的精神，但它至少符合列出的标准。

回答by Lukasz Szozda

Probably the easiest way is to use DUMP:

可能最简单的方法是使用DUMP：

SELECT COUNT(DISTINCT DUMP(hour)) AS distinct_count
FROM hours;

Output: 3

输出：3

DBFiddle Demo

DBFiddle 演示