很简单 where col_1 IS NULL AND col_2 IS NULL AND col_3 IS NULL AND col_4 IS NULL AND col_5 IS NULL AND col_6 IS NULL AND col_7 IS NULL AND col_8 IS NULL AND col_9 IS NULL AND col_10 IS NULL
这样可以?CREATE TABLE test(a NUMBER(5),b NUMBER(5),c NUMBER(5)); INSERT INTO test VALUES (100,NULL,null); INSERT INTO test VALUES (null,200,null); INSERT INTO test VALUES (300,NULL,null); COMMIT;SELECT column_name FROM ( SELECT 'A'column_name,Max(Length(a))column_len FROM test UNION ALL SELECT 'B'column_name,Max(Length(b))column_len FROM test UNION ALL SELECT 'C'column_name,Max(Length(c))column_len FROM test )WHERE column_len IS NULL;
怕是要用PL/SQL哦 这样是否可以: DECLARE CURSOR temp IS SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=Upper('TEST'); v_num NUMBER; BEGIN FOR i IN temp LOOP execute immediate 'SELECT Count(*) FROM test WHERE '||i.COLUMN_NAME||' IS NOT null' INTO v_num; IF v_num=0 THEN Dbms_Output.put_line('==columns that have no data=='||i.COLUMN_NAME); END IF; NULL; END LOOP; END;
where
col_1 IS NULL AND
col_2 IS NULL AND
col_3 IS NULL AND
col_4 IS NULL AND
col_5 IS NULL AND
col_6 IS NULL AND
col_7 IS NULL AND
col_8 IS NULL AND
col_9 IS NULL AND
col_10 IS NULL
INSERT INTO test VALUES (100,NULL,null);
INSERT INTO test VALUES (null,200,null);
INSERT INTO test VALUES (300,NULL,null);
COMMIT;SELECT column_name FROM
(
SELECT 'A'column_name,Max(Length(a))column_len FROM test UNION ALL
SELECT 'B'column_name,Max(Length(b))column_len FROM test UNION ALL
SELECT 'C'column_name,Max(Length(c))column_len FROM test
)WHERE column_len IS NULL;
小德兄,这个可以实现哎。我本来想要的是,假设一开始连列名都不知道,只知道表名,要从 user_tab_columns里找对应的列名,sql 该怎么写。您再帮我写一个?
这样是否可以:
DECLARE
CURSOR temp IS SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=Upper('TEST');
v_num NUMBER;
BEGIN
FOR i IN temp
LOOP
execute immediate 'SELECT Count(*) FROM test WHERE '||i.COLUMN_NAME||' IS NOT null' INTO v_num;
IF v_num=0 THEN
Dbms_Output.put_line('==columns that have no data=='||i.COLUMN_NAME);
END IF;
NULL;
END LOOP;
END;