SELECT * FROM( SELECT substr(SYS_CONNECT_BY_PATH(t.name,' '),2) FROM A t START WITH t.ID = 1 CONNECT BY PRIOR t.ID = t.ID - 1 ORDER BY LEVEL DESC) WHERE ROWNUM = 1
不明白我的意思? 我的意思是: ID,NAME 1 A 2 B 3 C 4 D view: A B C D 数据区
应该只能在存储过程的动态sql语句里面来实现
select max(t1),max(t2),max(t3),max(t4) from (select NAME t1,'' t2,'' t3,'' t4 from 表A where ID=1 union all select '',NAME,'','' from 表A where ID=2 union all select '','',NAME,'' from 表A where ID=3 union all select '','','',NAME from 表A where ID=4)
我曾经写过这样的sql,应该是用decode实现吧,你要的程序可能是这样子的吧,select max(v.A),max(v.B),max(v.C),max(v.D) from (select decode(s.name,'A',s.id,'0') A , decode(s.name,'B',s.id '0') B , decode(s.name,'C',s.id '0') C , decode(s.name,'D',s.id '0') D from tablename s ) v group by v.A,v.B,v.C,v.D
可能我刚才讲得有些不太明白select v.o, decode(v.name ,'A' ,v.id,'A') A, decode(v.name ,'B' ,v.id,'B') B, decode(v.name ,'C' ,v.id ,'C') C, decode(v.name ,'D' ,v.id ,'D') D from( select t.o,t.name ,t.id from tablename t where t.name in ('A','B','C','D') group by t.o,t.name ,t.id ) v 这里有三个字段:o,name,id,直接分组查询就行了.其实也挺简单的
FROM(
SELECT substr(SYS_CONNECT_BY_PATH(t.name,' '),2)
FROM A t
START WITH t.ID = 1
CONNECT BY PRIOR t.ID = t.ID - 1
ORDER BY LEVEL DESC)
WHERE ROWNUM = 1
我的意思是:
ID,NAME
1 A
2 B
3 C
4 D view: A B C D
数据区
(select NAME t1,'' t2,'' t3,'' t4 from 表A where ID=1
union all
select '',NAME,'','' from 表A where ID=2
union all
select '','',NAME,'' from 表A where ID=3
union all
select '','','',NAME from 表A where ID=4)
(select decode(s.name,'A',s.id,'0') A ,
decode(s.name,'B',s.id '0') B ,
decode(s.name,'C',s.id '0') C ,
decode(s.name,'D',s.id '0') D
from tablename s ) v
group by v.A,v.B,v.C,v.D
decode(v.name ,'A' ,v.id,'A') A,
decode(v.name ,'B' ,v.id,'B') B,
decode(v.name ,'C' ,v.id ,'C') C,
decode(v.name ,'D' ,v.id ,'D') D
from(
select t.o,t.name ,t.id
from tablename t
where t.name in ('A','B','C','D')
group by t.o,t.name ,t.id
) v 这里有三个字段:o,name,id,直接分组查询就行了.其实也挺简单的