sql - 如何动态选择非空值列？

Question

我创建了如下表格。我只想动态选择 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

Answer 1

以下查询生成您要通过一些动态 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;