请教个 oracle 查询问题

[qinhuione]

大家好，请问下 oracle 查询可以做以下实现吗？ 怎么添加空行？
(虚拟数据)
table_name A
column_name c1 c2 c3 c4 ... c50
column_value a v v v ... v
column_value a v v v ... v
column_value c v v v ... v
column_value c v v ... v
column_value c v v v ... v
column_value a v v v ... v
column_value a v v v ... v
column_value a v v ... v
column_value b v v v ... v
column_value b v v v ... v


如，根据 c1 key 查询 c1 in (a,b,c,d) 和 判断 c3 c4 的值是否为空
想要返回以下数据：
c1 c2 c3 c4 ... c50
d:没有数据
a v v v ... v
a v v v ... v
c v v v ... v
c v v ... v
c3:值为空
c v v v ... v
a v v v ... v
a v v v ... v
a v v ... v
c4:值为空
b v v v ... v
b v v v ... v

如果in条件后的key 在数据库找不到就在上面添加一行数据说没有数据
如果某一条数据的某一列值为空就在该数据下方添加一行数据说明

可以做这种实现吗？

[qinhuione]

补充一下； 不能用存储过程和临时表

[izj]

用with as 可以实现。。 不果貌似是临时表。。

[小海葵1]

WITH TEST AS ( SELECT 'A' AS STR,'1' AS STR1,'2' AS STR2,'3' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'A' AS STR,'1' AS STR1,'2' AS STR2,'' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'B' AS STR,'1' AS STR1,'2' AS STR2,'3' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'B' AS STR,'1' AS STR1,'2' AS STR2,'3' AS STR3,'' AS STR4 FROM DUAL UNION ALL SELECT 'C' AS STR,'1' AS STR1,'2' AS STR2,'3' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'C' AS STR,'1' AS STR1,'2' AS STR2,'' AS STR3,'' AS STR4 FROM DUAL ), TEST1 AS ( SELECT 'A' AS STR FROM DUAL UNION ALL SELECT 'B' AS STR FROM DUAL UNION ALL SELECT 'C' AS STR FROM DUAL UNION ALL SELECT 'D' AS STR FROM DUAL ) SELECT ROWNUM AS RN,TEST.* FROM TEST UNION SELECT * FROM( SELECT ROWNUM AS RN, TRIM(BOTH ',' FROM DECODE(STR,NULL,STRA || ':' || 'IS NOT EXIST', DECODE(STR3, NULL, 'STR3:IS NULL') || ',' || DECODE(STR4, NULL, 'STR4:IS NULL'))) AS STR,'' AS STR1,'' AS STR2,'' AS STR3,'' AS STR4 FROM (SELECT TEST1.STR AS STRA,TEST.* FROM TEST1 LEFT JOIN TEST ON TEST1.STR = TEST.STR)) WHERE STR IS NOT NULL ======================================= 1 1 A 1 2 3 4 2 2 A 1 2 4 3 2 STR3:IS NULL 4 3 B 1 2 3 4 5 4 B 1 2 3 6 4 STR4:IS NULL 7 5 C 1 2 3 4 8 6 C 1 2 9 6 STR3:IS NULL,STR4:IS NULL 10 7 D:IS NOT EXIST 这个是我写过最纠结的一个SQL。

[izj]

引用 3 楼 restbely 的回复:

WITH TEST AS ( SELECT 'A' AS STR,'1' AS STR1,'2' AS STR2,'3' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'A' AS STR,'1' AS STR1,'2' AS STR2,'' AS STR3,'4' AS STR4 FROM DUAL UNION ALL SELECT 'B……

哈哈哈。。

[qinhuione]

纠结吧，我也纠结