I was wondering about the possibility to count the null columns of row in SQL, I have a table Customer that has nullable values, simply I want a query that return an int of the number of null columns for certain row(certain customer).
我想知道是否有可能在SQL中计算行的空列,我有一个具有可空值的表客户,我只想要一个查询,该查询返回某个行(某个客户)的空列数的整数。
12 个解决方案
#1
28
This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don't have too many nullable columns to add up here...
该方法为空列分配1或0,并将它们全部相加。希望这里没有太多的可空列。
SELECT
((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)
+ (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)
+ (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)
...
...
+ (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
Note, you can also do this perhaps a little more syntactically cleanly with IF()
if your RDBMS supports it.
注意,如果RDBMS支持的话,您也可以使用IF()进行语法上更简洁的处理。
SELECT
(IF(col1 IS NULL, 1, 0)
+ IF(col2 IS NULL, 1, 0)
+ IF(col3 IS NULL, 1, 0)
...
...
+ IF(col10 IS NULL, 1, 0)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
I tested this pattern against a table and it appears to work properly.
我在一个表上测试了这个模式,它似乎正常工作。
#2
1
For ORACLE-DBMS only.
仅供oracle dbms。
You can use the NVL2 function:
可以使用NVL2函数:
NVL2( string1, value_if_not_null, value_if_null )
Here is a select with a similiar approach as Michael Berkowski suggested:
这是一个用相似的方法选择的方法,迈克尔·伯考斯基建议:
SELECT (NVL2(col1, 0, 1)
+ NVL2(col2, 0, 1)
+ NVL2(col3, 0, 1)
...
...
+ NVL2(col10, 0, 1)
) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
A more generic approach would be to write a PL/SQL-block and use dynamic SQL. You have to build a SELECT string with the NVL2 method from above for every column in the all_tab_columns of a specific table.
一种更通用的方法是编写PL/SQL块并使用动态SQL。您必须使用上面的NVL2方法为特定表的all_tab_columns中的每个列构建一个SELECT字符串。
#3
0
Unfortunately, in a standard SQL statement you will have to enter each column you want to test, to test all programatically you could use T-SQL. A word of warning though, ensure you are working with genuine NULLS, you can have blank stored values that the database will not recognise as a true NULL (I know this sounds strange).
不幸的是,在一个标准的SQL语句中,您必须输入您想要测试的每个列,以便以编程方式测试您可以使用的所有T-SQL。警告一下,确保您使用的是真正的空值,您可以拥有数据库无法识别为真正空值的空白存储值(我知道这听起来很奇怪)。
You can avoid this by capturing the blank values and the NULLS in a statement like this:
您可以通过以下语句捕获空值和空值来避免这种情况:
CASE WHEN col1 & '' = '' THEN 1 ELSE 0 END
Or in some databases such as Oracle (not sure if there are any others) you would use:
或者在某些数据库中,如Oracle(不确定是否有其他数据库),您将使用:
CASE WHEN col1 || '' = '' THEN 1 ELSE 0 END
#4
0
You don't state RDBMS. For SQL Server 2008...
你不RDBMS。SQL Server 2008…
SELECT CustomerId,
(SELECT COUNT(*) - COUNT(C)
FROM (VALUES(CAST(Col1 AS SQL_VARIANT)),
(Col2),
/*....*/
(Col9),
(Col10)) T(C)) AS NumberOfNulls
FROM Customer
#5
0
Depending on what you want to do, and if you ignore mavens, and if you use SQL Server 2012, you could to it another way. .
取决于您想要做什么,如果您忽略了mavens,如果您使用SQL Server 2012,您可以使用另一种方式。
The total number of candidate columns ("slots") must be known. 1. Select all the known "slots" column by column (they're known). 2. Unpivot that result to get a table with one row per original column. This works because the null columns don't unpivot, and you know all the column names. 3. Count(*) the result to get the number of non-nulls; subtract from that to get your answer.
必须知道候选列的总数(“slot”)。1。选择所有已知的“slot”列(它们是已知的)。2。取消对结果的透视,以获得每个原始列有一行的表。这是有效的,因为空列没有unpivot,而且你知道所有的列名。3所示。计数(*)获取非空值数的结果;减去它得到你的答案。
Like this, for 4 "seats" in a car
就像这样,在一辆车里买4个“座位”
select 'empty seats' = 4 - count(*)
from
(
select carId, seat1,seat2,seat3,seat4 from cars where carId = @carId
) carSpec
unpivot (FieldValue FOR seat in ([seat1],[seat2],[seat3],[seat4])) AS results
This is useful if you may need to do more later than just count the number of non-null columns, as it gives you a way to manipulate the columns as a set too.
如果您可能需要做更多的事情,而不仅仅是计算非空列的数量,那么这是非常有用的,因为它还为您提供了一种将列作为一个集合进行操作的方法。
#6
0
This will give you the number of columns which are not null. you can apply this appropriately
这将给出不为空的列数。您可以适当地应用它
SELECT ISNULL(COUNT(col1),'') + ISNULL(COUNT(col2),'') +ISNULL(COUNT(col3),'')
FROM TABLENAME
WHERE ID=1
#7
0
The below script gives you the NULL
value count within a row i.e. how many columns do not have values.
下面的脚本给出一行中的空值计数,即有多少列没有值。
{SELECT
*,
(SELECT COUNT(*)
FROM (VALUES (Tab.Col1)
,(Tab.Col2)
,(Tab.Col3)
,(Tab.Col4)) InnerTab(Col)
WHERE Col IS NULL) NullColumnCount
FROM (VALUES(1,2,3,4)
,(NULL,2,NULL,4)
,(1,NULL,NULL,NULL)) Tab(Col1,Col2,Col3,Col4) }
Just to demonstrate I am using an inline table in my example.
为了演示我在示例中使用的是内联表。
Try to cast or convert all column values to a common type it will help you to compare the column of different type.
尝试将所有列值转换为公共类型,这将帮助您比较不同类型的列。
#8
0
I haven't tested it yet, but I'd try to do it using a PL\SQL function
我还没有对它进行测试,但是我将尝试使用PL\SQL函数进行测试
CREATE OR REPLACE TYPE ANYARRAY AS TABLE OF ANYDATA
;
CREATE OR REPLACE Function COUNT_NULL
( ARR IN ANYARRAY )
RETURN number
IS
cnumber number ;
BEGIN
for i in 1 .. ARR.count loop
if ARR(i).column_value is null then
cnumber := cnumber + 1;
end if;
end loop;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
raise_application_error
(-20001,'An error was encountered - '
||SQLCODE||' -ERROR- '||SQLERRM);
END
;
Then use it in a select query like this
然后在这样的select查询中使用它
CREATE TABLE TEST (A NUMBER, B NUMBER, C NUMBER);
INSERT INTO TEST (NULL,NULL,NULL);
INSERT INTO TEST (1 ,NULL,NULL);
INSERT INTO TEST (1 ,2 ,NULL);
INSERT INTO TEST (1 ,2 ,3 );
SELECT ROWNUM,COUNT_NULL(A,B,C) AS NULL_COUNT FROM TEST;
Expected output
预期的输出
ROWNUM | NULL_COUNT
-------+-----------
1 | 3
2 | 2
3 | 1
4 | 0
#9
0
This is how i tried
我就是这样尝试的
CREATE TABLE #temptablelocal (id int NOT NULL, column1 varchar(10) NULL, column2 varchar(10) NULL, column3 varchar(10) NULL, column4 varchar(10) NULL, column5 varchar(10) NULL, column6 varchar(10) NULL);
INSERT INTO #temptablelocal
VALUES (1,
NULL,
'a',
NULL,
'b',
NULL,
'c')
SELECT *
FROM #temptablelocal
WHERE id =1
SELECT count(1) countnull
FROM
(SELECT a.ID,
b.column_title,
column_val = CASE b.column_title
WHEN 'column1' THEN a.column1
WHEN 'column2' THEN a.column2
WHEN 'column3' THEN a.column3
WHEN 'column4' THEN a.column4
WHEN 'column5' THEN a.column5
WHEN 'column6' THEN a.column6
END
FROM
( SELECT id,
column1,
column2,
column3,
column4,
column5,
column6
FROM #temptablelocal
WHERE id =1 ) a
CROSS JOIN
( SELECT 'column1'
UNION ALL SELECT 'column2'
UNION ALL SELECT 'column3'
UNION ALL SELECT 'column4'
UNION ALL SELECT 'column5'
UNION ALL SELECT 'column6' ) b (column_title) ) AS pop WHERE column_val IS NULL
DROP TABLE #temptablelocal
#10
0
Similary, but dynamically:
同样,但动态:
drop table if exists myschema.table_with_nulls;
create table myschema.table_with_nulls as
select
n1::integer,
n2::integer,
n3::integer,
n4::integer,
c1::character varying,
c2::character varying,
c3::character varying,
c4::character varying
from
(
values
(1,2,3,4,'a','b','c','d'),
(1,2,3,null,'a','b','c',null),
(1,2,null,null,'a','b',null,null),
(1,null,null,null,'a',null,null,null)
) as test_records(n1, n2, n3, n4, c1, c2, c3, c4);
drop function if exists myschema.count_nulls(varchar,varchar);
create function myschema.count_nulls(schemaname varchar, tablename varchar) returns void as
$BODY$
declare
calc varchar;
sqlstring varchar;
begin
select
array_to_string(array_agg('(' || trim(column_name) || ' is null)::integer'),' + ')
into
calc
from
information_schema.columns
where
table_schema in ('myschema')
and table_name in ('table_with_nulls');
sqlstring = 'create temp view count_nulls as select *, ' || calc || '::integer as count_nulls from myschema.table_with_nulls';
execute sqlstring;
return;
end;
$BODY$ LANGUAGE plpgsql STRICT;
select * from myschema.count_nulls('myschema'::varchar,'table_with_nulls'::varchar);
select
*
from
count_nulls;
Though I see that I didn't finish parametising the function.
虽然我看到我没有完成函数的参数化。
#11
0
My answer builds on Michael Berkowski's answer, but to avoid having to type out hundreds of column names, what I did was this:
我的回答建立在Michael Berkowski的答案之上,但为了避免输入数百个列名,我做的是:
Step 1: Get a list of all of the columns in your table
步骤1:获取表中所有列的列表
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';
Step 2: Paste the list in Notepad++ (any editor that supports regular expression replacement will work). Then use this replacement pattern
步骤2:将列表粘贴到Notepad++中(任何支持正则表达式替换的编辑器都可以)。然后使用这种替换模式
-
Search:
搜索:
^(.*)$
-
Replace:
替换:
\(CASE WHEN \1 IS NULL THEN 1 ELSE 0 END\) +
Step 3: Prepend SELECT identityColumnName,
and change the very last +
to AS NullCount FROM myTable
and optionally add an ORDER BY...
步骤3:Prepend选择identityColumnName,并将最后的+更改为myTable中的NullCount,并可选择添加一个ORDER BY…
SELECT
identityColumnName,
(CASE WHEN column001 IS NULL THEN 1 ELSE 0 END) +
-- ...
(CASE WHEN column200 IS NULL THEN 1 ELSE 0 END) AS NullCount
FROM
myTable
ORDER BY
NullCount DESC
#12
-1
There isn't a straightforward way of doing so like there would be with counting rows. Basically, you have to enumerate all the columns that might be null in one expression.
没有一种简单的方法可以做到像计算行那样。基本上,您必须枚举一个表达式中可能为空的所有列。
So for a table with possibly null columns a
, b,
c
, you could do this:
对于可能为空列ab c的表,你可以这样做:
SELECT key_column, COALESCE(a,0) + COALESCE(b,0) + COALESCE(c,0) null_col_count
FROM my_table
#1
28
This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don't have too many nullable columns to add up here...
该方法为空列分配1或0,并将它们全部相加。希望这里没有太多的可空列。
SELECT
((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)
+ (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)
+ (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)
...
...
+ (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
Note, you can also do this perhaps a little more syntactically cleanly with IF()
if your RDBMS supports it.
注意,如果RDBMS支持的话,您也可以使用IF()进行语法上更简洁的处理。
SELECT
(IF(col1 IS NULL, 1, 0)
+ IF(col2 IS NULL, 1, 0)
+ IF(col3 IS NULL, 1, 0)
...
...
+ IF(col10 IS NULL, 1, 0)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
I tested this pattern against a table and it appears to work properly.
我在一个表上测试了这个模式,它似乎正常工作。
#2
1
For ORACLE-DBMS only.
仅供oracle dbms。
You can use the NVL2 function:
可以使用NVL2函数:
NVL2( string1, value_if_not_null, value_if_null )
Here is a select with a similiar approach as Michael Berkowski suggested:
这是一个用相似的方法选择的方法,迈克尔·伯考斯基建议:
SELECT (NVL2(col1, 0, 1)
+ NVL2(col2, 0, 1)
+ NVL2(col3, 0, 1)
...
...
+ NVL2(col10, 0, 1)
) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id
A more generic approach would be to write a PL/SQL-block and use dynamic SQL. You have to build a SELECT string with the NVL2 method from above for every column in the all_tab_columns of a specific table.
一种更通用的方法是编写PL/SQL块并使用动态SQL。您必须使用上面的NVL2方法为特定表的all_tab_columns中的每个列构建一个SELECT字符串。
#3
0
Unfortunately, in a standard SQL statement you will have to enter each column you want to test, to test all programatically you could use T-SQL. A word of warning though, ensure you are working with genuine NULLS, you can have blank stored values that the database will not recognise as a true NULL (I know this sounds strange).
不幸的是,在一个标准的SQL语句中,您必须输入您想要测试的每个列,以便以编程方式测试您可以使用的所有T-SQL。警告一下,确保您使用的是真正的空值,您可以拥有数据库无法识别为真正空值的空白存储值(我知道这听起来很奇怪)。
You can avoid this by capturing the blank values and the NULLS in a statement like this:
您可以通过以下语句捕获空值和空值来避免这种情况:
CASE WHEN col1 & '' = '' THEN 1 ELSE 0 END
Or in some databases such as Oracle (not sure if there are any others) you would use:
或者在某些数据库中,如Oracle(不确定是否有其他数据库),您将使用:
CASE WHEN col1 || '' = '' THEN 1 ELSE 0 END
#4
0
You don't state RDBMS. For SQL Server 2008...
你不RDBMS。SQL Server 2008…
SELECT CustomerId,
(SELECT COUNT(*) - COUNT(C)
FROM (VALUES(CAST(Col1 AS SQL_VARIANT)),
(Col2),
/*....*/
(Col9),
(Col10)) T(C)) AS NumberOfNulls
FROM Customer
#5
0
Depending on what you want to do, and if you ignore mavens, and if you use SQL Server 2012, you could to it another way. .
取决于您想要做什么,如果您忽略了mavens,如果您使用SQL Server 2012,您可以使用另一种方式。
The total number of candidate columns ("slots") must be known. 1. Select all the known "slots" column by column (they're known). 2. Unpivot that result to get a table with one row per original column. This works because the null columns don't unpivot, and you know all the column names. 3. Count(*) the result to get the number of non-nulls; subtract from that to get your answer.
必须知道候选列的总数(“slot”)。1。选择所有已知的“slot”列(它们是已知的)。2。取消对结果的透视,以获得每个原始列有一行的表。这是有效的,因为空列没有unpivot,而且你知道所有的列名。3所示。计数(*)获取非空值数的结果;减去它得到你的答案。
Like this, for 4 "seats" in a car
就像这样,在一辆车里买4个“座位”
select 'empty seats' = 4 - count(*)
from
(
select carId, seat1,seat2,seat3,seat4 from cars where carId = @carId
) carSpec
unpivot (FieldValue FOR seat in ([seat1],[seat2],[seat3],[seat4])) AS results
This is useful if you may need to do more later than just count the number of non-null columns, as it gives you a way to manipulate the columns as a set too.
如果您可能需要做更多的事情,而不仅仅是计算非空列的数量,那么这是非常有用的,因为它还为您提供了一种将列作为一个集合进行操作的方法。
#6
0
This will give you the number of columns which are not null. you can apply this appropriately
这将给出不为空的列数。您可以适当地应用它
SELECT ISNULL(COUNT(col1),'') + ISNULL(COUNT(col2),'') +ISNULL(COUNT(col3),'')
FROM TABLENAME
WHERE ID=1
#7
0
The below script gives you the NULL
value count within a row i.e. how many columns do not have values.
下面的脚本给出一行中的空值计数,即有多少列没有值。
{SELECT
*,
(SELECT COUNT(*)
FROM (VALUES (Tab.Col1)
,(Tab.Col2)
,(Tab.Col3)
,(Tab.Col4)) InnerTab(Col)
WHERE Col IS NULL) NullColumnCount
FROM (VALUES(1,2,3,4)
,(NULL,2,NULL,4)
,(1,NULL,NULL,NULL)) Tab(Col1,Col2,Col3,Col4) }
Just to demonstrate I am using an inline table in my example.
为了演示我在示例中使用的是内联表。
Try to cast or convert all column values to a common type it will help you to compare the column of different type.
尝试将所有列值转换为公共类型,这将帮助您比较不同类型的列。
#8
0
I haven't tested it yet, but I'd try to do it using a PL\SQL function
我还没有对它进行测试,但是我将尝试使用PL\SQL函数进行测试
CREATE OR REPLACE TYPE ANYARRAY AS TABLE OF ANYDATA
;
CREATE OR REPLACE Function COUNT_NULL
( ARR IN ANYARRAY )
RETURN number
IS
cnumber number ;
BEGIN
for i in 1 .. ARR.count loop
if ARR(i).column_value is null then
cnumber := cnumber + 1;
end if;
end loop;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
raise_application_error
(-20001,'An error was encountered - '
||SQLCODE||' -ERROR- '||SQLERRM);
END
;
Then use it in a select query like this
然后在这样的select查询中使用它
CREATE TABLE TEST (A NUMBER, B NUMBER, C NUMBER);
INSERT INTO TEST (NULL,NULL,NULL);
INSERT INTO TEST (1 ,NULL,NULL);
INSERT INTO TEST (1 ,2 ,NULL);
INSERT INTO TEST (1 ,2 ,3 );
SELECT ROWNUM,COUNT_NULL(A,B,C) AS NULL_COUNT FROM TEST;
Expected output
预期的输出
ROWNUM | NULL_COUNT
-------+-----------
1 | 3
2 | 2
3 | 1
4 | 0
#9
0
This is how i tried
我就是这样尝试的
CREATE TABLE #temptablelocal (id int NOT NULL, column1 varchar(10) NULL, column2 varchar(10) NULL, column3 varchar(10) NULL, column4 varchar(10) NULL, column5 varchar(10) NULL, column6 varchar(10) NULL);
INSERT INTO #temptablelocal
VALUES (1,
NULL,
'a',
NULL,
'b',
NULL,
'c')
SELECT *
FROM #temptablelocal
WHERE id =1
SELECT count(1) countnull
FROM
(SELECT a.ID,
b.column_title,
column_val = CASE b.column_title
WHEN 'column1' THEN a.column1
WHEN 'column2' THEN a.column2
WHEN 'column3' THEN a.column3
WHEN 'column4' THEN a.column4
WHEN 'column5' THEN a.column5
WHEN 'column6' THEN a.column6
END
FROM
( SELECT id,
column1,
column2,
column3,
column4,
column5,
column6
FROM #temptablelocal
WHERE id =1 ) a
CROSS JOIN
( SELECT 'column1'
UNION ALL SELECT 'column2'
UNION ALL SELECT 'column3'
UNION ALL SELECT 'column4'
UNION ALL SELECT 'column5'
UNION ALL SELECT 'column6' ) b (column_title) ) AS pop WHERE column_val IS NULL
DROP TABLE #temptablelocal
#10
0
Similary, but dynamically:
同样,但动态:
drop table if exists myschema.table_with_nulls;
create table myschema.table_with_nulls as
select
n1::integer,
n2::integer,
n3::integer,
n4::integer,
c1::character varying,
c2::character varying,
c3::character varying,
c4::character varying
from
(
values
(1,2,3,4,'a','b','c','d'),
(1,2,3,null,'a','b','c',null),
(1,2,null,null,'a','b',null,null),
(1,null,null,null,'a',null,null,null)
) as test_records(n1, n2, n3, n4, c1, c2, c3, c4);
drop function if exists myschema.count_nulls(varchar,varchar);
create function myschema.count_nulls(schemaname varchar, tablename varchar) returns void as
$BODY$
declare
calc varchar;
sqlstring varchar;
begin
select
array_to_string(array_agg('(' || trim(column_name) || ' is null)::integer'),' + ')
into
calc
from
information_schema.columns
where
table_schema in ('myschema')
and table_name in ('table_with_nulls');
sqlstring = 'create temp view count_nulls as select *, ' || calc || '::integer as count_nulls from myschema.table_with_nulls';
execute sqlstring;
return;
end;
$BODY$ LANGUAGE plpgsql STRICT;
select * from myschema.count_nulls('myschema'::varchar,'table_with_nulls'::varchar);
select
*
from
count_nulls;
Though I see that I didn't finish parametising the function.
虽然我看到我没有完成函数的参数化。
#11
0
My answer builds on Michael Berkowski's answer, but to avoid having to type out hundreds of column names, what I did was this:
我的回答建立在Michael Berkowski的答案之上,但为了避免输入数百个列名,我做的是:
Step 1: Get a list of all of the columns in your table
步骤1:获取表中所有列的列表
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';
Step 2: Paste the list in Notepad++ (any editor that supports regular expression replacement will work). Then use this replacement pattern
步骤2:将列表粘贴到Notepad++中(任何支持正则表达式替换的编辑器都可以)。然后使用这种替换模式
-
Search:
搜索:
^(.*)$
-
Replace:
替换:
\(CASE WHEN \1 IS NULL THEN 1 ELSE 0 END\) +
Step 3: Prepend SELECT identityColumnName,
and change the very last +
to AS NullCount FROM myTable
and optionally add an ORDER BY...
步骤3:Prepend选择identityColumnName,并将最后的+更改为myTable中的NullCount,并可选择添加一个ORDER BY…
SELECT
identityColumnName,
(CASE WHEN column001 IS NULL THEN 1 ELSE 0 END) +
-- ...
(CASE WHEN column200 IS NULL THEN 1 ELSE 0 END) AS NullCount
FROM
myTable
ORDER BY
NullCount DESC
#12
-1
There isn't a straightforward way of doing so like there would be with counting rows. Basically, you have to enumerate all the columns that might be null in one expression.
没有一种简单的方法可以做到像计算行那样。基本上,您必须枚举一个表达式中可能为空的所有列。
So for a table with possibly null columns a
, b,
c
, you could do this:
对于可能为空列ab c的表,你可以这样做:
SELECT key_column, COALESCE(a,0) + COALESCE(b,0) + COALESCE(c,0) null_col_count
FROM my_table