SQL 如何返回按 NULL 和 NOT NULL 分组的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/239545/
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
How do I return my records grouped by NULL and NOT NULL?
提问by Stewart Johnson
I have a table that has a processed_timestampcolumn -- if a record has been processed then that field contains the datetime it was processed, otherwise it is null.
我有一个包含一processed_timestamp列的表——如果一条记录已被处理，那么该字段包含它被处理的日期时间，否则它为空。
I want to write a query that returns two rows:
我想编写一个返回两行的查询：
NULL xx -- count of records with null timestamps
NOT NULL yy -- count of records with non-null timestamps
Is that possible?
那可能吗？
Update:The table is quite large, so efficiency is important. I could just run two queries to calculate each total separately, but I want to avoid hitting the table twice if I can avoid it.
更新：桌子很大，所以效率很重要。我可以只运行两个查询来分别计算每个总数，但如果可以避免的话，我想避免两次击中表。
采纳答案by trunkc
Oracle:
甲骨文：
group by nvl2(field, 'NOT NULL', 'NULL')
group by nvl2(field, 'NOT NULL', 'NULL')
回答by Stefan Gehrig
In MySQL you could do something like
在 MySQL 中，您可以执行以下操作
SELECT
IF(ISNULL(processed_timestamp), 'NULL', 'NOT NULL') as myfield,
COUNT(*)
FROM mytable
GROUP BY myfield
回答by Tomalak
In T-SQL (MS SQL Server), this works:
在 T-SQL (MS SQL Server) 中，这有效：
SELECT
CASE WHEN Field IS NULL THEN 'NULL' ELSE 'NOT NULL' END FieldContent,
COUNT(*) FieldCount
FROM
TheTable
GROUP BY
CASE WHEN Field IS NULL THEN 'NULL' ELSE 'NOT NULL' END
回答by paxdiablo
Try the following, it's vendor-neutral:
尝试以下操作，它是供应商中立的：
select
'null ' as type,
count(*) as quant
from tbl
where tmstmp is null
union all
select
'not null' as type,
count(*) as quant
from tbl
where tmstmp is not null
After having our local DB2 guru look at this, he concurs: none of the solutions presented to date (including this one) can avoid a full table scan (of the table if timestamp is not indexed, or of the indexotherwise). They all scan every record in the table exactly once.
在让我们的本地 DB2 专家看了之后，他同意：迄今为止提出的所有解决方案（包括这个解决方案）都不能避免全表扫描（如果时间戳没有被索引，则对表进行全表扫描，否则对索引进行扫描）。它们都只扫描表中的每条记录一次。
All the CASE/IF/NVL2() solutions do a null-to-string conversion for each row, introducing unnecessary load on the DBMS. This solution does not have that problem.
所有 CASE/IF/NVL2() 解决方案都对每一行进行空到字符串的转换，从而在 DBMS 上引入了不必要的负载。这个解决方案没有这个问题。
回答by ADEpt
If it's oracle then you can do:
如果是oracle，那么你可以这样做：
select decode(field,NULL,'NULL','NOT NULL'), count(*)
from table
group by decode(field,NULL,'NULL','NOT NULL');
I'm sure that other DBs allow for similar trick.
我确信其他数据库允许类似的技巧。
回答by James Green
Stewart,
斯图尔特，
Maybe consider this solution. It is (also!) vendor non-specific.
也许考虑这个解决方案。它是（也！）供应商非特定的。
SELECT count([processed_timestamp]) AS notnullrows,
count(*) - count([processed_timestamp]) AS nullrows
FROM table
As for efficiency, this avoids 2x index seeks/table scans/whatever by including the results on one row. If you absolutely require 2 rows in the result, two passes over the set may be unavoidable because of unioning aggregates.
至于效率，这避免了 2x 索引查找/表扫描/通过将结果包含在一行中。如果您绝对需要结果中的 2 行，则由于联合聚合，可能不可避免地需要对集合进行两次传递。
Hope this helps
希望这可以帮助
回答by Refael
Another way in T-sql (sql-server)
T-sql中的另一种方式（sql-server）
select count(case when t.timestamps is null
then 1
else null end) NULLROWS,
count(case when t.timestamps is not null
then 1
else null end) NOTNULLROWS
from myTable t
回答by Tom
Another MySQL method is to use the CASEoperator, which can be generalised to more alternatives than IF():
另一种 MySQL 方法是使用CASE运算符，它可以推广到比IF()以下更多的选择：
SELECT CASE WHEN processed_timestamp IS NULL THEN 'NULL'
ELSE 'NOT NULL' END AS a,
COUNT(*) AS n
FROM logs
GROUP BY a
回答by dkretz
If your database has an efficient COUNT(*) function for a table, you could COUNT whichever is the smaller number, and subtract.
如果您的数据库对表具有有效的 COUNT(*) 函数，则可以对较小的数字进行 COUNT，然后减去。
回答by Jatin Sanghvi
SQL Server (starting with 2012):
SQL Server（从 2012 年开始）：
SELECT IIF(ISDATE(processed_timestamp) = 0, 'NULL', 'NON NULL'), COUNT(*)
FROM MyTable
GROUP BY ISDATE(processed_timestamp);
