我创建了如下表格。我只想动态选择 NOT NULL 列,如(NO,NAME,SAL_1)。无需选择 SAL,SAL_2。
注意:最初我不知道列值。
create table sample(no integer,name varchar(20),sal integer,sal_1 integer,sal_2 integer);
insert into sample(name,sal_1) values('aaa',10);
insert into sample(no,name,sal_1) values(20,'',20);
insert into sample(sal_1) values(30);
select * from sample;
数据如下
NO NAME SAL SAL_1 SAL_2 20 (null) (null) 20 (null) (null) (null) (null) 30 (null) (null) aaa (null) 10 (null)
预期操作:
NO NAME SAL_1
20 (null) 20
(null) (null) 30
(null) aaa 10
最佳答案
以下查询生成您要通过一些动态 SQL 过程执行的查询。
不幸的是,我不太了解 Oracle 语法,以下是 PostgreSQL 中的语法,但我想您只需将 string_agg
更改为相关的 Oracle 函数连接字符串即可。
WITH columns (c) AS (
SELECT 'no' WHERE EXISTS (SELECT * FROM sample WHERE no IS NOT NULL)
UNION
SELECT 'name' WHERE EXISTS (SELECT * FROM sample WHERE name IS NOT NULL)
UNION
SELECT 'sal' WHERE EXISTS (SELECT * FROM sample WHERE sal IS NOT NULL)
UNION
SELECT 'sal_1' WHERE EXISTS (SELECT * FROM sample WHERE sal_1 IS NOT NULL)
UNION
SELECT 'sal_2' WHERE EXISTS (SELECT * FROM sample WHERE sal_2 IS NOT NULL)
)
SELECT 'SELECT ' || string_agg(c, ', ') || ' FROM sample;' FROM columns;
https://stackoverflow.com/questions/44904200/