SQL 计算SQL中一行中的空列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8596500/
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
Count the Null columns in a row in SQL
提问by Islam
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 中计算行的空列的可能性，我有一个表 Customer 具有可为空值，只是我想要一个查询，该查询返回某个行（某些客户）的空列数的 int。
回答by Michael Berkowski
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.
请注意，IF()如果您的 RDBMS 支持，您也可以在语法上更简洁地执行此操作。
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.
我在一张桌子上测试了这个模式，它似乎工作正常。
回答by Drew Chapin
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 IS NULL THEN 1 ELSE 0 END\) +
搜索：
^(.*)$代替：
\(CASE WHEN IS NULL THEN 1 ELSE 0 END\) +
Step 3:Prepend SELECT identityColumnName,and change the very last +to AS NullCount FROM myTableand optionally add an ORDER BY...
第 3 步：预先添加并将SELECT identityColumnName,最后一个更改+为AS NullCount FROM myTable并可选地添加一个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
回答by Stephan Schielke
For ORACLE-DBMS only.
仅适用于ORACLE-DBMS。
You can use the NVL2function:
您可以使用NVL2函数：
NVL2( string1, value_if_not_null, value_if_null )
Here is a select with a similiar approach as Michael Berkowski suggested:
以下是 Michael Berkowski 建议的具有类似方法的选择：
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 NVL2method from above for every column in the all_tab_columnsof a specific table.
更通用的方法是编写 PL/SQL 块并使用动态 SQL。您必须使用上面的NVL2方法为特定表的all_tab_columns中的每一列构建一个 SELECT 字符串。
回答by Neeraj Kumar
The below script gives you the NULLvalue count within a row i.e. how many columns do not have values.
下面的脚本为您NULL提供一行中的值计数，即有多少列没有值。
{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.
尝试将所有列值强制转换或转换为通用类型，这将帮助您比较不同类型的列。
回答by Khaled.K
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
然后在这样的选择查询中使用它
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
回答by Mayank
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
回答by Mark Andrew
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.
虽然我看到我没有完成函数的参数化。
回答by Matt Donnan
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。警告的话，请确保您使用的是真正的 NULL，您可以拥有数据库不会识别为真正 NULL 的空白存储值（我知道这听起来很奇怪）。
You can avoid this by capturing the blank values and the NULLS in a statement like this:
您可以通过在如下语句中捕获空白值和 NULL 来避免这种情况：
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
回答by Martin Smith
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
回答by philw
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. .
取决于你想要做什么，如果你忽略了 Maven，如果你使用 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.
必须知道候选列（“槽”）的总数。1. 逐列选择所有已知的“插槽”（它们是已知的）。2. 旋转该结果以获得每个原始列一行的表格。这是有效的，因为空列不会反转，并且您知道所有列名称。3. Count(*) 结果得到非空的个数；从中减去以获得您的答案。
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.
如果您以后可能需要做的不仅仅是计算非空列的数量，这很有用，因为它也为您提供了一种将列作为集合进行操作的方法。
