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
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
[Quote=引用 6 楼 wxmwxmwxm 的回复:]
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)
[/Quote]
兄弟,这个写法我知道,但有一点的是,我的表中数据的个数是比较多的,而且也是未知的,不可能用你这样写的。
[Quote=引用 6 楼 wxmwxmwxm 的回复:]
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)
[/Quote]
我表A有 n条记录 怎么写呢?
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)
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