如何返回按NULL和NOTNULL分组的记录?

SELECT 
    IF(ISNULL(processed_timestamp), 'NULL', 'NOT NULL') as myfield, 
    COUNT(*) 
FROM mytable 
GROUP BY myfield

在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

神谕:

按nvl2分组(字段“非空”、“空”)

请尝试以下操作,它与供应商无关:

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

在让我们当地的DB2专家看了这一点之后,他表示同意:迄今为止提出的解决方案(包括这一个)都无法避免完整的表扫描(如果时间戳未被索引,则扫描表,或者以其他方式扫描索引)。他们都会精确地扫描表中的每条记录一次。

select decode(field,NULL,'NULL','NOT NULL'), count(*)
from table
group by decode(field,NULL,'NULL','NOT NULL');

我相信其他DBs也会采用类似的技巧。

斯图尔特,

也许考虑一下这个解决方案。它(也是!)供应商不特定。

SELECT count([processed_timestamp]) AS notnullrows, 
       count(*) - count([processed_timestamp]) AS nullrows 
FROM table

至于效率,通过将结果包含在一行中,可以避免2x索引查找/表扫描/任何操作。如果结果中绝对需要2行,则由于合并聚合,在集合上进行两次传递可能是不可避免的。

CASE operator ,可以概括为比 IF() :

SELECT CASE WHEN processed_timestamp IS NULL THEN 'NULL' 
            ELSE 'NOT NULL' END AS a,
       COUNT(*) AS n 
       FROM logs 
       GROUP BY a

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 

如果您的数据库有一个表的有效计数(*)函数,则可以计算较小的数字,然后进行减法。

SQL Server(从2012年开始):

SELECT IIF(ISDATE(processed_timestamp) = 0, 'NULL', 'NON NULL'), COUNT(*)
FROM MyTable
GROUP BY ISDATE(processed_timestamp);

我个人喜欢Pax的解决方案,但如果您绝对只需要返回一行(正如我最近所做的),那么在MS SQL Server 2005/2008中,您可以使用CTE“堆叠”这两个查询

with NullRows (countOf)
AS
(
    SELECT count(*) 
    FORM table 
    WHERE [processed_timestamp] IS NOT NULL
)
SELECT count(*) AS nulls, countOf
FROM table, NullRows
WHERE [processed_timestamp] IS NULL
GROUP BY countOf

希望这有帮助

[T-SQL]:

select [case], count(*) tally
from (
  select 
  case when [processed_timestamp] is null then 'null'
  else 'not null'
  end [case]
  from myTable
) a 

您可以在case语句中添加任何其他要形成分区的值,例如今天、昨天、中午到下午2点之间、周四下午6点之后。

Select Sum(Case When processed_timestamp IS NULL
                         Then 1
                         Else 0
                 End)                                                               not_processed_count,
          Sum(Case When processed_timestamp Is Not NULL
                         Then 1
                         Else 0
                 End)                                                               processed_count,
          Count(1)                                                                total
From table

编辑:没有仔细阅读,这一行只返回一行。

SELECT COUNT(*), COUNT(TIME_STAMP_COLUMN)
FROM TABLE;

count(*)返回所有行的计数

count(column_name)返回不为空的行数,因此

SELECT COUNT(*) - COUNT(TIME_STAMP_COLUMN) NUL_COUNT,
                  COUNT(TIME_STAMP_COLUMN) NON_NUL_COUNT
FROM TABLE

我应该做这项工作。