首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何仅检索Oracle中任意行中至少有一个非空值的列

问如何仅检索Oracle中任意行中至少有一个非空值的列
EN

Stack Overflow用户
提问于 2018-05-03 18:39:31
回答 3查看 911关注 0票数 2

我有如下https://ibb.co/mkGp67的表结构和数据

我希望SQL查询只检索其中至少有一个非空值的那些列的数据,在上面的情况下,我希望数据是

https://ibb.co/mz9967

也就是说,我不需要列Col2,Col5和Col6,也不需要所有空值的列是不固定的。

请让我知道的SQL查询,检索数据,只有那些列没有空值与数据如上。

EN

回答 3

Stack Overflow用户

发布于 2018-05-08 07:58:05

因此,由于您没有给出正式的表结构,并且您似乎混淆了数字和字符,所以我将尽我最大的努力进行查询,至少会产生您想要的结果。

将表foo创建为( col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10) );

select * CASE cust1 WHEN null then ' null‘else cust1 as cust1 end,CASE cust2 WHEN null then ' null’else cust1 as cust1 end,CASE cust3 WHEN null then ' null‘else cust1 as cust1 end,CASE cust4 WHEN null then 'null’else cust1 as cust1 end,CASE cust5 WHEN null then 'null‘else cust1 as cust1 end,CASE cust6 WHEN null then 'null’else cust1 as cust1 end from foo;

票数 0
EN

Stack Overflow用户

发布于 2018-05-08 13:34:05

代码语言:javascript
复制
As per below query , I able to get not null columns at row-level col1,col3 and col4.

Query :
select 'col1' as "Name",col1 from temp
 where  exists (select  1 
 from temp
 group by to_char(col1)
 having (count(to_char(col1)))> 0)
 union all 
 select 'col2' as "Name",to_char(col2) from temp
 where  exists (select  1 
 from temp
 group by to_char(col2)
 having (count(to_char(col2)))> 0)
 union all
 select 'col3' as "Name" , to_char(col3) from temp
 where  exists (select  1 
 from temp
 group by to_char(col3)
 having (count(to_char(col3)))> 0)
 union all
 select 'col4'as "Name" , to_char(col4) from temp
 where  exists (select  1 
 from temp
 group by to_char(col4)
 having (count(to_char(col4)))> 0)
 union all
 select 'col5' as "Name" , to_char(col5) from temp
 where  exists (select  1 
 from temp
 group by to_char(col5)
 having (count(to_char(col5)))> 0)
 union all
 select 'col6' as "Name" , to_char(col6) from temp
 where  exists (select  1 
 from temp
 group by to_char(col6)
 having (count(to_char(col6)))> 0)

output:

col1    A
col1    B
col1    C
col1    D
col3    10
col3    20
col3     - 
col3    10
col4    12
col4    23
col4    34
col4    43

I tried to make this output of rows to columns but I couldn't make it in single query ... Hope this will be helpful ... 
票数 0
EN

Stack Overflow用户

发布于 2018-06-27 18:01:34

我通常会分三步来做这件事。

首先,确保表统计信息是最新的。检查last_analyzed是否晚于对表的最后一次更改。

代码语言:javascript
复制
SELECT last_analyzed FROM user_tables WHERE table_name = 'MYTABLE';

如果有疑问,请使用以下命令更新统计信息

代码语言:javascript
复制
BEGIN dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE'); END;
/

现在,视图user_tab_columns有一个列num_nulls。这是此列为NULL的行数。如果该值与表中的行数相同,则所有行都为NULL。这可用于让Oracle生成所需的SQL:

代码语言:javascript
复制
WITH 
  qtab AS (SELECT owner, table_name, num_rows
             FROM all_tables 
            WHERE owner='SCOTT'            -- change to your schema
              AND table_name='EMPLOYEES'   -- change to your table name
           ),
  qcol AS (SELECT owner, table_name, column_name, column_id
             FROM qtab t 
             JOIN all_tab_columns c USING (owner, table_name)
            WHERE c.nullable = 'N'         -- protected by NOT NULL constraint 
               OR c.num_nulls = 0          -- never NULL
               OR c.num_nulls < t.num_rows -- at least 1 row is NOT NULL
              )  
              )  
SELECT 'SELECT '||LISTAGG(column_name,',') WITHIN GROUP (ORDER BY column_id)||
        ' FROM '||owner||'.'||table_name||';' AS my_query
  FROM qcol
 GROUP BY owner, table_name;

这将输出如下查询

代码语言:javascript
复制
SELECT col1, col3, col4, col5 FROM myschema.mytable;

现在可以执行此查询以显示列值。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50152990

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档