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

6 回答

• -1

  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)
  

  回复于 2024-04-17T03:59:08+08:00
• 2

  你要做的是在整个结果中 Build 对每一行的依赖 . 事实上，这并不是你想要的 . 如果在每一列的每一行都有一个值为'0'的情况下，只需考虑分支 - 突然，结果集的模式会增长，以包含所有以前的"empty"列 . 您're effectively growing the badness of ' * ' exponentially, now your result set is not dependent on just the table'的元数据 - 但您的整个结果集取决于普通数据 .

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

  回复于 2024-04-17T03:59:08+08:00
• 3

  使用以下：

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

  Table_Name 必须相应更换......

  回复于 2024-04-17T03:59:08+08:00
• 2

  我不认为这可以在一个查询中完成 . 您可能需要一些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;
  

  回复于 2024-04-17T03:59:08+08:00
• 0

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

  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是否标识"empty"列 .
  参考：ALL_TAB_COLUMNS，ALL_TABLES .

  回复于 2024-04-17T03:59:08+08:00
• 4

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

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

  回复于 2024-04-17T03:59:08+08:00