Question

我的表格有5个“选择”领域。我愿指出,有5个牢房全部无效,有1个非核区,等等。

我尝试了两件事,例如:

select 
 count(*),
 ( (if field1 is null then 1 else 0) +
   (if field2 is null then 1 else 0) +
 etc.

但这当然不奏效。

理想的情况是,我期待着像这样的产出。

Nulls   Cnt
0        200
1        345
...
5        40

是否有一个棘手的解决办法?

Answer 1

关键词不是if,而是case,你必须使用end/code>,以结束case。



这里可以问:

WITH subQuery AS
(
  SELECT My_Table.*, (CASE WHEN My_Table.field1 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field2 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field3 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field4 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field5 IS NULL THEN 1 ELSE 0 END ) NumberOfNullFields
    FROM My_Table
)
SELECT NumberOfNullFields, COUNT(*)
  FROM subQuery
 GROUP BY NumberOfNullFields;

Answer 2

尽管WHEN在计算案件上没有任何错误,但我只想看到有另一种方式。

WITH SAMPLEDATA AS(--just generate some data with nulls for 5 cols
    select  level ,
            (case when mod(level,2) = 0 then 1 else null end) colA,
            (case when mod(level,3) = 0 then 1 else null end) colB,
            (case when mod(level,5) = 0 then 1 else null end) colC,
            (case when mod(level,7) = 0 then 1 else null end) colD,
            (case when mod(level,11) = 0 then 1 else null end) colE

      from dual
      connect by level < 1000
    ), --utilize the count(Aggregate) s avoidance of nulls to our summation advantage
    nullCols as(
    SELECT COUNT(COLA) aNotNull
           ,cOUNT(*)-COUNT(COLA) aNull
           ,count(colB) bNotNull
           ,cOUNT(*)-count(colB) bNull
           ,count(colc) cNotNull
           ,cOUNT(*)-count(colc) cNull
           ,count(cold) dNotNull
           ,cOUNT(*)-count(cold) dNull
           ,count(cole) eNotNull
           ,cOUNT(*)-count(cole) eNull
           , cOUNT(*) TotalCountOfRows
     from SAMPLEDATA  )
    SELECT (select count(*) from sampledata where cola is null and colb is null and colc is null and cold is null and cole is null) allIsNull     
           ,nullCols.*
    FROM nullCols;

ALLISNULL              ANOTNULL               ANULL                  BNOTNULL               BNULL                  CNOTNULL               CNULL                  DNOTNULL               DNULL                  ENOTNULL               ENULL                  TOTALCOUNTOFROWS       
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
207                    499                    500                    333                    666                    199                    800                    142                    857                    90                     909                    999

利用这一方法

If expression in count(expression) evaluates to null, it is not counted: as noted from here

如上所述,这种方法并不能雄辩地把所有无分立栏完全归为一栏。只想看到,如果没有化学文摘社国际协会的逻辑,这是否可行。

友情链接