SQL 计数空字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8247768/
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
SQL Count empty fields
提问by Tom
I'm not sure if this is possible or if it is, how to do it -
我不确定这是否可行，或者如果可行，该怎么做 -
I have the following data in a database -
我在数据库中有以下数据 -
id | improve | timeframe | criteria | impact
-------+------------+-------------+-----------+---------
1 | | Test | Test | Test
2 | Test | | Test |
3 | | Test | |
-------+------------+-------------+-----------+---------
Ignoring the id column, how can I determine the number of fields out of the remaining 12 that are not null using an SQL query?
忽略 id 列，如何使用 SQL 查询确定剩余 12 个不为空的字段数？
I have started with -
我已经开始 -
SELECT improve, timeframe, impact, criteria
FROM data
WHERE improve IS NOT NULL
AND timeframe IS NOT NULL
AND impact IS NOT NULL
AND criteria IS NOT NULL;
This only returns the number of rows, ie. 3.
这仅返回行数，即。3.
Any ideas?
有任何想法吗？
Thanks.
谢谢。
回答by Cito
SELECT count(improve) + count(timeframe) + count(impact) + count(criteria) FROM data
回答by brian chandley
Something like this may get you going in the right direction
像这样的事情可能会让你朝着正确的方向前进
SELECT
SUM(CASE WHEN improve IS NULL THEN 0 ELSE 1 END +
CASE WHEN timeframe IS NULL THEN 0 ELSE 1 END +
CASE WHEN criteria IS NULL THEN 0 ELSE 1 END +
CASE WHEN impact IS NULL THEN 0 ELSE 1 END)
from
data
回答by user2710961
SELECT id, COUNT(improve) + COUNT(timeframe) + COUNT(impact) + COUNT(criteria) FROM data GROUP BY id;
SELECT id, COUNT(improve) + COUNT(timeframe) + COUNT(impact) + COUNT(criteria) FROM data GROUP BY id;
回答by Shawn Spencer
IF you're using SQL Server,use DATALENGTH().
如果您使用的是 SQL Server，请使用 DATALENGTH()。
SELECT improve, timeframe, impact, criteria
FROM data
WHERE DATALENGTH(improve) > 0
AND DATALENGTH(timeframe) > 0
AND DATALENGTH(impact) > 0
AND DATALENGTH(criteria) >0;
DATALENGTH returns the length of the string in bytes, including trailing spaces. It sounded as though you're OK with blank fields, so DATALENGTH does the job. Otherwise, you could also use LEN(), which would trim any trailing space.
DATALENGTH 以字节为单位返回字符串的长度，包括尾随空格。听起来好像你对空白字段没问题，所以 DATALENGTH 可以完成这项工作。否则，您还可以使用 LEN()，它会修剪任何尾随空格。
IF you are using MySQL,you can use CHARACTER_LENGTH, which removes trailing white space and then gives you a character count of the field you want to check.
如果您使用 MySQL，则可以使用 CHARACTER_LENGTH，它会删除尾随空格，然后为您提供要检查的字段的字符数。
回答by Michael
SELECT Sum(case when improve is null then 0 else 1 end +
case when timeframe is null then 0 else 1 end +
case when impact is null then 0 else 1 end +
case when criteria is null then 0 else 1 end)
FROM data
group by improve, timeframe, impact, criteria
