SQL Oracle：查找只有空值的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4627938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Finding Columns with only null values
提问by Jorge Valois
I have a table with a lot of columns and a type column.
我有一个包含很多列和一个类型列的表。
Some columns seem to be always empty for a specific type.
对于特定类型，某些列似乎始终为空。
I want to create a view for each type and only show the relevant columns for each type. Working under the assumption that if a column has ONLY null values for a specific type, then that columns should not be part of the view, how can you find that out with queries?
我想为每种类型创建一个视图，并且只显示每种类型的相关列。假设如果一列只有特定类型的空值，那么该列不应该是视图的一部分，您如何通过查询找到这一点？
Is there a SELECT [columnName] FROM [table] WHERE [columnValues] ARE ALL [null]
是否有 SELECT [columnName] FROM [table] WHERE [columnValues] ARE ALL [null]
I know I COMPLETELY made it all up above... I'm just trying to get the idea across. Thanks in advance!
我知道我完全做到了上面的一切......我只是想把这个想法传达出来。提前致谢！
回答by Fer Lopez
SELECT t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
AND t.table_name = 'YOUR_TABLE_NAME'
AND t.num_distinct = 0
回答by René Nyffenegger
select
count(col_1),
count(col_2),
count(col_3)
from
<table>
returns how many records per column have a non-null value (at least in Oracle, that is.)
返回每列有多少记录具有非空值（至少在 Oracle 中是这样）。
For example
例如
drop table tq84_count_nulls;
create table tq84_count_nulls (
col_1 varchar(50),
col_2 number,
col_3 date
);
insert into tq84_count_nulls values (null, null, null);
insert into tq84_count_nulls values ('xx', null, null);
insert into tq84_count_nulls values (null, 42, null);
insert into tq84_count_nulls values ('yy', 12, null);
select
count(col_1),
count(col_2),
count(col_3)
from
tq84_count_nulls;
returns
回报
COUNT(COL_1) COUNT(COL_2) COUNT(COL_3)
------------ ------------ ------------
2 2 0
indicating that col_3 consists of nulls only.
表示 col_3 仅包含空值。
This idea can then be used to create the desired view.
然后可以使用这个想法来创建所需的视图。
The table now needs also the *group_id*:
该表现在还需要 *group_id*：
drop table tq84_count_nulls;
create table tq84_count_nulls (
col_1 varchar(50),
col_2 number,
col_3 date,
group_id varchar(2)
);
insert into tq84_count_nulls values (null, null, null, 'a');
insert into tq84_count_nulls values ('xx', null, null, 'a');
insert into tq84_count_nulls values (null, 42, null, 'a');
insert into tq84_count_nulls values ('yy', 12, null, 'a');
insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null, 42, null, 'b');
insert into tq84_count_nulls values (null, 12, null, 'b');
create or replace view nulls_per_type as
with n as (
select
count(col_1) col_1_count,
count(col_2) col_2_count,
count(col_3) col_3_count,
group_id
from
tq84_count_nulls
group by
group_id
),
o as (
select case col_1_count when 0 then 'COL_1 is always 0 for ' || group_id else null end u from n union all
select case col_2_count when 0 then 'COL_2 is always 0 for ' || group_id else null end u from n union all
select case col_3_count when 0 then 'COL_3 is always 0 for ' || group_id else null end u from n
)
select * from o where u is not null;
Which, when selected returns:
选择返回时，哪个：
select * from nulls_per_type;
COL_1 is always 0 for b
COL_3 is always 0 for a
COL_3 is always 0 for b
回答by dataduck
I think you can solve this using metaprogramming. Use a cursor to loop through each type and column, and use 'not exists' to check if the column is empty. For example:
我认为您可以使用元编程来解决这个问题。使用游标遍历每个类型和列，并使用“不存在”检查列是否为空。例如：
CREATE TABLE result_table (type VARCHAR(50), column VARCHAR(50))
CURSOR c IS
SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = &table_name;
CURSOR ct IS
SELECT DISTINCT type_name FROM &table_name;
BEGIN
FOR t in ct
LOOP
FOR r in c
LOOP
--If you're confused about how this works, replace 'EXECUTE IMMEDIATE'
--with print or something and look at the output
EXECUTE IMMEDIATE
'INSERT INTO result_table SELECT ''' ||
t.type_name || ''', ''' || r.COLUMN_NAME ||
''' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ' ||
&table_name || ' WHERE t.type_name = ''' || t.type_name ||
''' AND ' || r.COLUMN_NAME || ' IS NOT NULL);';
END LOOP
END LOOP
SELECT * FROM result_table
Apologies if there's a mistake in the syntax somewhere, I have nothing to check this on.
抱歉，如果某处的语法有错误，我没有什么可检查的。
回答by Mutturaj
You can identify by using the below query :
您可以使用以下查询进行识别：
select * from (select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t)
where col is null;
And you want to delete null column rows here is query :
你想删除空列的行是查询：
delete from
(select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t)
where col is null;
回答by vinsinraw
To find rows that have a null-value, use the "is null" condition.
要查找具有空值的行，请使用“为空”条件。
select * from table_A where table_col1 is null;
To do the reverse, and find all the rows with a non-null value, use the "is not null" condition:
要进行相反的操作，并找到具有非空值的所有行，请使用“不为空”条件：
select * from table_A where table_col1 is not null;
Nulls in range comparisons:
范围比较中的空值：
select * from table_A where table_col1 < 15 or table_col1 is null;
回答by Jorge Valois
After looking at @Gerrat and @BQ's comments, I thouht I could get the details I need in the following way: I have a legacy table that has N different types. All types share columns, and have exclusive columns.
查看@Gerrat 和@BQ 的评论后，我认为我可以通过以下方式获得所需的详细信息：我有一个包含 N 种不同类型的旧表。所有类型共享列，并具有独占列。
I can create a view for each type with all columns, then use all_tab_columnsto get all column names where the "num_nulls" is less than the total number of rows for that specific type.
我可以为每个类型的所有列创建一个视图，然后使用all_tab_columns获取所有列名，其中“num_nulls”小于该特定类型的总行数。
From there it should be easy to gather columns that are used for each type and create the views.
从那里应该很容易收集用于每种类型的列并创建视图。
Thoughts?
想法？
回答by isreal Obaro
SELECT tablecolumn, tablecolumn2, ... FROM TABLENAME
WHERE column IS NOT NULL
回答by BQ.
Something like this?
像这样的东西？
SELECT column1, column2, column3 -- and so on
FROM tableA
WHERE columnX IS NULL
AND columnY IS NULL
AND columnZ IS NULL;
Obviously, you can use that in a CREATE VIEW...statement if you like as well.
显然，CREATE VIEW...如果您愿意，也可以在语句中使用它。
