您当前的位置: 如果数据库存储的数据格式像这样
CODE NAME1 NAME2 NAME3
1 A1 NULL NULL
1 A11 NULL NULL
2 NULL A2 NULL
3 NULL NULL A3
我想把CODE2 CODE3的内容合并到CODE1 的第一行
变成
NAME1 NAME2 NAME3
A1 A2 A3
A11 NULL NULL
这样SQL如何实现？
------解决思路----------------------
看不出A1,A2,A3有什么联系
------解决思路----------------------
with TT as (select 1 code,'A1' name1,null name2,null name3 from dual union all
select 1 code,'A11' name1,null name2,null name3 from dual union all
select 2 code,null name1,'A2' name2,null name3 from dual union all
select 3 code,null name1,null name2,'A3' name3 from dual)
select A.name1,B.name2,C.name3 from (select row_number() over (order by name1) rn,name1 from tt) A
full outer join (select row_number() over (order by name2) rn,name2 from tt) B on A.rn=B.rn
full outer join (select row_number() over (order by name3) rn,name3 from tt) C on A.rn=C.rn
where not (name1 is null and name2 is null and name3 is null)
order by 1;
------解决思路----------------------
只是简单地将数据堆在一起，之间没有联系。不符合关系数据库的基本思路。
但是可以做到
SELECT t1.name1,t2.name2,t3.name3
FROM(SELECT ROWNUM rn,name1 FROM 表 WHERE name1 IS NOT NULL)t1
FULL JOIN (SELECT ROWNUM rn,name2 FROM 表 WHERE name2 IS NOT NULL)t2
ON t1.rn=t2.rn
FULL JOIN (SELECT ROWNUM rn,name3 FROM 表 WHERE name3 IS NOT NULL) t3
ON NVL(t1.rn,t2.rn)=t3.rn