大家好,请问下 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 在数据库找不到就在上面添加一行数据说没有数据
如果某一条数据的某一列值为空就在该数据下方添加一行数据说明可以做这种实现吗?
(虚拟数据)
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 在数据库找不到就在上面添加一行数据说没有数据
如果某一条数据的某一列值为空就在该数据下方添加一行数据说明可以做这种实现吗?
解决方案 »
- 关于CLEAR的问题。。
- 求解:IMP-00020: 用于列缓冲区大小 (1) 的长整数列过大
- 为什么在这张表上做查询速度这么慢?
- ORACLE9.2.7,用D:\oracle\oradata目录下的文件,如何恢复数据库?请高手指点
- PL/SQL问题 帮帮忙啊 在线等
- 求救,oracle数据库备份与恢复的问题!!!!!
- 写存储过程时insert table表名是个变量该怎么写?
- 这样的SQL怎么写?
- 请教oracle9i命令行中如何显示一个表的DDL
- 数据传输问题----急!!
- oracle 并发 大量数据(成千上万 )需要修改时候怎么处理?
- Proc中利用dual表判断两个日期间隔180天之内
不果貌似是临时表。。
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。