Oracle:如何计算空行和非空行 [英] Oracle: How to count null and non-null rows

问题描述

我有一个表，其中有两列可能是null(以及其他一些列).我想计算有多少行具有列a，b，两个列都没有设置为null.

I have a table with two columns that might be null (as well as some other columns). I would like to count how many rows that have column a, b, both and neither columns set to null.

在一个查询中使用Oracle可以做到吗?还是我必须为每个查询创建一个查询?不能使用group by或其他一些我可能不知道的东西?

Is this possible with Oracle in one query? Or would I have to create one query for each? Can't use group by or some other stuff I might not know about for example?

推荐答案

COUNT(expr) 将计算expr不为空的行数，因此您可以使用以下表达式来计算空数:

COUNT(expr) will count the number of rows where expr is not null, thus you can count the number of nulls with expressions like these:

SELECT count(a) nb_a_not_null,
       count(b) nb_b_not_null,
       count(*) - count(a) nb_a_null,
       count(*) - count(b) nb_b_null,
       count(case when a is not null and b is not null then 1 end)nb_a_b_not_null
       count(case when a is null and b is null then 1 end) nb_a_and_b_null
  FROM my_table

这篇关于Oracle:如何计算空行和非空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！