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

2021-12-19 00:00:00 sql null select oracle

我有一个包含数百列的表，其中许多列为空，我想要我的 select 语句，以便只返回那些包含值的列.这将帮助我更好地分析数据.类似的东西:

I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like:

从表名中选择(非空列)；

Select (non null columns) from tablename;

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

I want to select all columns which have at least one non-null value.

这能做到吗?

推荐答案

看看统计信息，可能对你有用:

Have a look as statistics information, it may be useful for you:

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.

For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.

相关文章