with t as( select '1' c1,'A' c2,'a' c3 from dual union all select '1','A','b' from dual union all select '1','B','c' from dual ) select t1.c1, t1.c3 a, t2.c3 b from (select c1, c3 from t where t.c2 = 'A') t1, (select c1, c3 from t where t.c2 = 'B') t2 where t1.c1=t2.c1 C1 A B -- - - 1 b c 1 a c
支持2楼的思路,用两个表之间的连接很好。可以优化点,我认为没有必要用with语句建表,直接用t1表就行了 SELECT m.C1, m.C3 A, n.C3 B FROM (SELECT C1, C3 FROM T1 WHERE T1.C2 = 'A') m, (SELECT C1, C3 FROM T1 WHERE T1.C2 = 'B') n WHERE m.C1 = n.C1
http://www.itpub.net/forum.php?mod=viewthread&tid=1017026
select '1' c1,'A' c2,'a' c3 from dual
union all
select '1','A','b' from dual
union all
select '1','B','c' from dual
)
select t1.c1, t1.c3 a, t2.c3 b
from (select c1, c3 from t where t.c2 = 'A') t1,
(select c1, c3 from t where t.c2 = 'B') t2 where t1.c1=t2.c1
C1 A B
-- - -
1 b c
1 a c
SELECT m.C1, m.C3 A, n.C3 B
FROM (SELECT C1, C3 FROM T1 WHERE T1.C2 = 'A') m,
(SELECT C1, C3 FROM T1 WHERE T1.C2 = 'B') n
WHERE m.C1 = n.C1