table A{
ID //编号
MC //名称
}
table B{
ID //编号
ZBID //关联A表的ID
FLD1 //字段1
}
表A每条记录在B表中都有2条记录对应;
要求查询出来的结果是:
A.ID, A.MC, B.FLD1, B.FLD2
B.FLD1, B.FLD2就是与表A对应的表B的两条记录字段FLD1.
这样好写吗?
ID //编号
MC //名称
}
table B{
ID //编号
ZBID //关联A表的ID
FLD1 //字段1
}
表A每条记录在B表中都有2条记录对应;
要求查询出来的结果是:
A.ID, A.MC, B.FLD1, B.FLD2
B.FLD1, B.FLD2就是与表A对应的表B的两条记录字段FLD1.
这样好写吗?
where a.id=b.zbid
group by a.id,a.mc
select a.ID,
a.MC,
sum(decode(BB.rn,1,BB.ID)) as FLD1_ID,
sum(decode(BB.rn,1,BB.FLD1)) as FLD1,
sum(decode(BB.rn,2,BB.ID)) as FLD2_ID,
sum(decode(BB.rn,2,BB.FLD1)) as FLD2,
sum(decode(BB.rn,3,BB.ID)) as FLD3_ID,
sum(decode(BB.rn,3,BB.FLD1)) as FLD3,
sum(decode(BB.rn,4,BB.ID)) as FLD4_ID,
sum(decode(BB.rn,4,BB.FLD1)) as FLD4
from A a,
(select b.ZBID,b.ID,b.FLD1,row_number() over(partition by b.ZBID order by b.ZBID) rn
from B b
)BB
where a.ID = BB.ZBID
group by a.ID,a.MC;