现情况为:
表M
Id name
------------
a aName
b bName
c cName表Am(m_id对应表M的id)
Id m_id
-------------
1 a
2 c表Ad(为Am的子表)
Id am_id qty
--------------------
1 1 10
2 1 10
3 2 10表Bm(m_id对应表M的id)
Id m_id
-------------
1 a
2 b表Bd(为Bm的子表)
Id bm_id qty
--------------------
1 1 10
2 2 10
3 2 10当可能还有Cm,Cd;Dm,Dd...多个关联子集,
现在要得到的结果:m.id m.name ad.qty bd.qty
------------------------------
a aName 20 10
b bName 20 -->ad.qty为空
c bName 10 -->bd.qty为空
表M
Id name
------------
a aName
b bName
c cName表Am(m_id对应表M的id)
Id m_id
-------------
1 a
2 c表Ad(为Am的子表)
Id am_id qty
--------------------
1 1 10
2 1 10
3 2 10表Bm(m_id对应表M的id)
Id m_id
-------------
1 a
2 b表Bd(为Bm的子表)
Id bm_id qty
--------------------
1 1 10
2 2 10
3 2 10当可能还有Cm,Cd;Dm,Dd...多个关联子集,
现在要得到的结果:m.id m.name ad.qty bd.qty
------------------------------
a aName 20 10
b bName 20 -->ad.qty为空
c bName 10 -->bd.qty为空
m,Am,(select am_id,sum(qty) qty from Ad group by am_id) Aad,
Bm,(select bm_id,sum(qty) qty from Bd group by bm_id) Bbd,
where m.id=Am.m_id
and m.id=Bm.id
and Am.id=Aad.am_id(+)
and Bm.id=Bbd.bm_id(+)
================================================================
现情况为:
表M
Id name
------------
a aName
b bName
c cName表Am(m_id对应表M的id)
Id m_id
-------------
1 a
2 c表Ad(为Am的子表)
Id am_id qty
--------------------
1 1 10
2 1 10
3 2 10表Bm(m_id对应表M的id)
Id m_id
-------------
1 a
2 b表Bd(为Bm的子表)
Id bm_id qty
--------------------
1 1 10
2 2 10
3 2 10当可能还有Cm,Cd;Dm,Dd...多个关联子集,
现在要得到的结果:m.id m.name ad.qty bd.qty
------------------------------
a aName 20 10
b bName 20 -->ad.qty为空
c bName 10 -->bd.qty为空
======================================================
不对,我要求的是-->ad.qty为空,-->bd.qty为空
from m left join am on m.id = am.m_id
left join Ad on am.m_id = ad.m_id
left join bm on m.id = bm.m_id
left join Bd on am.m_id = bd.m_id