如何从表中选择具有非空值的列？

Question

我有一个包含数百列的表，其中许多列为空，并且我希望有我的 select 语句，以便只返回那些包含值的列。这将帮助我更好地分析数据。类似于：

从表名中选择（非空列）；

我想选择至少具有一个非空值的所有列。

这可以做到吗？

Answer 1

看看统计信息，它可能对您有用：

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';

  NUM_ROWS
----------
        14

SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
  2  where owner='SCOTT' and table_name='EMP' order by column_id;

COLUMN_NAME                    N NUM_DISTINCT  NUM_NULLS
------------------------------ - ------------ ----------
EMPNO                          N           14          0
ENAME                          Y           14          0
JOB                            Y            5          0
MGR                            Y            6          1
HIREDATE                       Y           13          0
SAL                            Y           12          0
COMM                           Y            4         10
DEPTNO                         Y            3          0

8 rows selected.

例如您可以检查 NUM_NULLS = NUM​​_ROWS 是否识别“空”列。
参考：ALL_TAB_COLUMNS，ALL_TABLES。

Answer 2

使用以下内容：

SELECT *
FROM information_schema.columns
WHERE table_name = 'Table_Name' and is_nullable = 'NO'

Table_Name 必须相应地替换...

Answer 3

select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;

这是获取非空列的简单代码。

Answer 4

我认为这不能在单个查询中完成。您可能需要一些 plsql 来首先测试哪些列包含数据，并根据该信息组合一个语句。当然，如果表中的数据发生更改，您必须重新创建语句。

declare

   l_table          varchar2(30) := 'YOUR_TABLE';
   l_statement      varchar2(32767);
   l_test_statement varchar2(32767);

   l_contains_value pls_integer;

   -- select column_names from your table
   cursor c is
      select column_name
            ,nullable
        from user_tab_columns
       where table_name = l_table;

begin
   l_statement := 'select ';
   for r in c
   loop
      -- If column is not nullable it will always contain a value
      if r.nullable = 'N'
      then
         -- add column to select list.
         l_statement := l_statement || r.column_name || ',';
      else
         -- check if there is a row that has a value for this column
         begin
            l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' ||
                                r.column_name || ' is not null)';
            dbms_output.put_line(l_test_statement);
            execute immediate l_test_statement
               into l_contains_value;


            -- Yes, add column to select list
            l_statement := l_statement || r.column_name || ',';
         exception
            when no_data_found then
               null;
         end;

      end if;
   end loop;

   -- create a select statement
   l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table;

end;

Answer 5

select rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') col
from (select column_name
from user_tab_columns
where table_name='<table_name>' and low_value is not null)

Answer 6

该块确定表中的所有列，在动态 SQL 中循环遍历它们并检查它们是否为空，然后构造非空查询的 DBMS 输出查询。

您所要做的就是运行返回的查询。

我已经排除了 PK 和 BLOB 列。
显然，这对于一一列地遍历来说非常慢，并且对于非常热的表来说并不是很好，因为数据可能变化太快，但这对我来说很有效，因为我控制了开发环境中的流量。

DECLARE
  l_table_name      VARCHAR2(255) := 'XXXX';
  l_counter         NUMBER;
  l_sql             CLOB;

BEGIN
  FOR r_col IN (SELECT * 
                FROM user_tab_columns tab_col
                WHERE table_name = l_table_name
                AND data_type NOT IN ('BLOB')
                AND column_name NOT IN (SELECT column_name 
                                        FROM user_cons_columns con_col
                                        JOIN user_constraints cons ON con_col.constraint_name = cons.constraint_name AND con_col.table_name = cons.table_name
                                        WHERE con_col.table_name = tab_col.table_name
                                        AND constraint_type = 'P')
                ORDER BY column_id) 
  LOOP

    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_table_name||' WHERE '||r_col.column_name||' IS NOT NULL'
    INTO l_counter;
    
    IF l_counter > 0 THEN
      IF  l_sql IS NULL THEN 
        l_sql := r_col.column_name;
      ELSE   
        l_sql :=  l_sql||','||r_col.column_name;
      END IF;
    END IF;
   
  END LOOP;  

  l_sql :=  'SELECT '||l_sql||CHR(10)
         ||'FROM '||l_table_name;
          
  ----------
  DBMS_OUTPUT.put_line(l_sql);  
END;

Answer 7

您要求做的是建立对整个结果中每一行的依赖关系。事实上，这并不是您想要的。试想一下，如果在一行中每一列的值为“0”，结果集的架构突然增长到包含所有以前的“空”列，会产生什么后果。您实际上正在以指数方式增加“*”的坏处，现在您的结果集不仅仅依赖于表的元数据 - 但您的整个结果集依赖于纯数据。

你要做的只是选择有你想要的字段，而不是偏离这个简单的计划。