我有如下https://ibb.co/mkGp67的表结构和数据
我希望SQL查询只检索其中至少有一个非空值的那些列的数据,在上面的情况下,我希望数据是
也就是说,我不需要列Col2,Col5和Col6,也不需要所有空值的列是不固定的。
请让我知道的SQL查询,检索数据,只有那些列没有空值与数据如上。
发布于 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;
发布于 2018-05-08 13:34:05
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 ... 发布于 2018-06-27 18:01:34
我通常会分三步来做这件事。
首先,确保表统计信息是最新的。检查last_analyzed是否晚于对表的最后一次更改。
SELECT last_analyzed FROM user_tables WHERE table_name = 'MYTABLE';如果有疑问,请使用以下命令更新统计信息
BEGIN dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE'); END;
/现在,视图user_tab_columns有一个列num_nulls。这是此列为NULL的行数。如果该值与表中的行数相同,则所有行都为NULL。这可用于让Oracle生成所需的SQL:
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;这将输出如下查询
SELECT col1, col3, col4, col5 FROM myschema.mytable;现在可以执行此查询以显示列值。
https://stackoverflow.com/questions/50152990
复制相似问题