我有一个包含数百列的表,其中许多列为空,我想要我的 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.
这篇关于如何从具有非空值的表中选择列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!