表结构如下:
T1表(父表)字段1
A
B
CT2表(子表)
字段1 字段X
A X1
A X2
A X3
B Y1
C Z1
C Z2==》查询结果如下
字段1 字段X1 字段X2
A X1 X2
B Y1
C Z1 Z2把T2表的列(字段X)变成T1表行。 如果A表对应B表多于2条数据,那么就取两条(如A X3数据不需要)
如果少于两条,为空(如B 只有)可以就送100分
T1表(父表)字段1
A
B
CT2表(子表)
字段1 字段X
A X1
A X2
A X3
B Y1
C Z1
C Z2==》查询结果如下
字段1 字段X1 字段X2
A X1 X2
B Y1
C Z1 Z2把T2表的列(字段X)变成T1表行。 如果A表对应B表多于2条数据,那么就取两条(如A X3数据不需要)
如果少于两条,为空(如B 只有)可以就送100分
SQL> SELECT T1.FIELD1,
2 MAX(DECODE(RN,1,FIELDX,NULL)) FIELDX1,
3 MAX(DECODE(RN,2,FIELDX,NULL)) FIELDX2
4 FROM T1,
5 (SELECT T2.*,
6 ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELDX) RN
7 FROM T2
8 )TT
9 WHERE T1.FIELD1 = TT.FIELD1
10 GROUP BY T1.FIELD1;FIELD1 FIELDX1 FIELDX2
------ ------- -------
A X1 X2
B Y1
C Z1 Z2
不需要做关联啊。
如果有其他字段并且都要查出来需要关联下
SQL> select * from t2;C1 C2
---------- ----------
A X1
A X2
A X3
B Y1
C Z1
C Z26 rows selectedSQL>
SQL> select c1, max(decode(rn, 1, c2)) x1, max(decode(rn, 2, c2)) x2
2 from (select c1, c2, rn
3 from (SELECT C1,
4 C2,
5 ROW_NUMBER() OVER(partition by c1 order by c2) rn
6 from t2)
7 where rn <= 2)
8 group by c1
9 ;C1 X1 X2
---------- ---------- ----------
A X1 X2
B Y1
C Z1 Z2
max(case n.px when 1 then n.字段X else '' end) 字段X1,
max(case n.px when 2 then n.字段X else '' end) 字段X2
from t1 m,
(select t.* , (select count(1) from t2 where 字段1 = t.字段1 and 字段X < t.字段X) + 1 px from t2 t) n
where m.字段1 = n.字段1
group by m.字段1