SQL COUNT（）将NULL计为不同的值

I wanted to make sure this query is doing what I want it to do. I'm using Oracle SQL Developer version 4.02.15.21.

我想确保此查询正在执行我想要的操作。我正在使用Oracle SQL Developer版本4.02.15.21。

Basically I have an ID column and a Value column (both CHAR), and I need to check if some IDs have multiple Values. If there are some I want to know which IDs these are. Each ID should only have 1 Value -- so every time the ID is listed, it should always have that Value, and not a different value (most IDs will be listed more than once).

基本上我有一个ID列和一个Value列(都是CHAR),我需要检查一些ID是否有多个值。如果有一些我想知道这些是哪些ID。每个ID应该只有1个值 - 所以每次列出ID时,它应该始终具有该值,而不是不同的值(大多数ID将被列出多次)。

This would be simple but the twist is that I need to count NULL values as a distinct value. I'm looking for NULL to be treated just like any other unique Value if that makes sense. Meaning, an ID's value can be NULL or NOT NULL but I need to be able to see if there are any other different values associated with the same ID (whether they are NULL or NOT NULL). So I wrote this query:

这很简单但是我需要将NULL值计算为一个不同的值。如果有意义的话,我正在寻找像其他任何唯一值一样对待的NULL。意思是,ID的值可以是NULL或NOT NULL但我需要能够查看是否存在与同一ID相关联的任何其他不同值(无论它们是NULL还是NOT NULL)。所以我写了这个查询:

--showing ITEM IDs which have more than one value associated --

SELECT I.ID, COUNT( DISTINCT (CASE WHEN I.VALUE IS NULL THEN '1' ELSE I.VALUE END) ) 
FROM ITEMS I
GROUP BY I.ID
HAVING COUNT( DISTINCT (CASE WHEN I.VALUE IS NULL 
                             THEN '1' ELSE I.VALUE END) ) > 1
ORDER BY I.ID

The idea is to turn the NULL values into '1's (actual values are 10 lettered CHAR strings, so '1' will always be unique) so that COUNT will count the NULL values, and so that if there is an ID listed 8 times with NULL for the Value in each case, it will still show that ID only has 1 Value associated with it ('1').

我们的想法是将NULL值转换为'1'(实际值是10个字母的CHAR字符串,因此'1'将始终是唯一的),以便COUNT将计算NULL值,并且如果有一个ID列出8次,则对于每种情况下的值为NULL,它仍将显示该ID只有1个与之关联的值('1')。

Does this look correct? It's for tables with thousands of entries and so far all my results have come up with no rows. I'm not sure if this is correct or not. Oh and I don't care about the time-wise performance or anything like that.

这看起来是否正确?它适用于包含数千个条目的表格,到目前为止,我的所有结果都没有出现任何行。我不确定这是否正确。哦,我不关心时间表现或类似的事情。

Thanks for taking a look.

谢谢参观。

2 个解决方案

#1

I would do this:

我会这样做:

SELECT count(coalesce( I.VALUE , 1)) FROM table;

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm#SQLRF00617

#2

This mostly is correct. Since nulls are essentially nothing in Sql, thay cannot be counted, so doing the CASE is a nice solution. But to make sure that the results you receive are the correct results, run this query without the HAVING and compare the results to results of the original query and table using JOINs.

这大多是正确的。由于空值在Sql中基本上没有,因此无法计算,所以做CASE是一个很好的解决方案。但是为了确保您收到的结果是正确的结果,请在没有HAVING的情况下运行此查询,并使用JOIN将结果与原始查询和表的结果进行比较。

#1