三张表
//袖子
CREATE TABLE T1(
C11 NUMBER,PRIMARY KEY
C12 VARCHAR2(50)
)
//棉袄
CREATE TABLE T2(
C21 NUMBER,PRIMARY KEY
C22 VARCHAR2(50)
)
//袖子和棉袄的关联表
CREATE TABLE T3(
C31 NUMBER PRIMARY KEY,
C32 NUMBER,
C33 NUMBER
)
T3.C32 引用T1.C1
T3.C33 引用T2.C1要求:
查询与T3关联两端的T1的内容,在一列上面显示出来
类似:棉袄a 袖子a 袖子b
棉袄b 袖子1 (没袖子)
。。
//袖子
CREATE TABLE T1(
C11 NUMBER,PRIMARY KEY
C12 VARCHAR2(50)
)
//棉袄
CREATE TABLE T2(
C21 NUMBER,PRIMARY KEY
C22 VARCHAR2(50)
)
//袖子和棉袄的关联表
CREATE TABLE T3(
C31 NUMBER PRIMARY KEY,
C32 NUMBER,
C33 NUMBER
)
T3.C32 引用T1.C1
T3.C33 引用T2.C1要求:
查询与T3关联两端的T1的内容,在一列上面显示出来
类似:棉袄a 袖子a 袖子b
棉袄b 袖子1 (没袖子)
。。
nvl(T2.C22,'没棉袄),
nvl(T1.C12,'没袖子')
from T3,T2,T1 where T3.C32=T1.C1(+) and T3.C33=T2.C1(+)
nvl(T2.C22,'没棉袄),
nvl(T1.C12,'没袖子'),
nvl(T1.C12,'没袖子')
from T3,T2,T1 where T3.C32=T1.C1(+) and T3.C33=T2.C1(+)
(select c22 from t2 where t2.c21=t3.c33) as 棉袄,c31
from t3;
用这个查出来你看看
你就明白你的这个是典型的行转列的问题了网上搜一下就OK了
from(
select c32,c33, row_number()over(partition by c32) r from t3
)a
group by c32;select c32,min(c33) c33a, nullif(max(c33),min(c33)) c33b
from t3
group by c32;select t2.c22, t1a.c12 c12a, t1b.c12 c12b
from (
select c32,min(c33) c33a, nullif(max(c33),min(c33)) c33b
from t3
group by c32;
)a
inner join t2 on t2.c21 = a.c32
left join t1 t1a on t1a.c11 = a.c33a
left join t1 t1b on t1b.c11 = a.c33b
;