sql求解,sql字段空值

发布网友发布时间：2022-04-08 18:41

共1个回答

热心网友时间：2022-04-08 20:11

看样子像oracle，其他数据库的话也基本类似写法。

创建表数据

create table t
(fenzu varchar2(1),
wei1 int,
wei2 int,
wei3 int,
wei4 int,
wei5 int);

insert into t values ('a',1,1,1,1,1);
insert into t values ('a',1,1,1,1,1);
insert into t values ('a',1,null,1,1,1);
insert into t values ('a',1,1,1,1,1);
insert into t values ('b',1,1,1,1,1);
insert into t values ('b',1,1,1,1,1);
insert into t values ('b',1,null,1,null,null);
insert into t values ('c',1,1,1,1,1);
insert into t values ('c',1,1,1,1,1);
insert into t values ('c',1,1,1,1,1);
commit;

执行sql：

select (t1.cnt - nvl(t2.cnt, 0)) / t1.cnt
  from (select count(*) cnt
          from (select fenzu, count(*) from t group by fenzu) s1) t1,
       (select count(*) cnt
          from (select fenzu, count(*)
                  from t
                 where wei1 is null
                    or wei2 is null
                    or wei3 is null
                    or wei4 is null
                    or wei5 is null
                 group by fenzu) s2) t2

结果：