两个表情况
表名:wu_plan
ID plan model corp_code plannum prixis
1 00001 exx22 nokia 2000 0
2 00002 lc001 sony 3000 0
表名:wu_bom
ID plan pact amount
1 00001 aa1 300
2 00001 aa2 200
3 00002 bb1 500
4 00002 bb2 800
5 00002 bb3 400
查询这两个表中plan唯一,每一个plan中,amount最少的,plannum大于prixis的记录
结果是:
ID plan model corp_code plannum prixis pact amount
1 00001 exx22 nokia 2000 0 a2 200
2 00002 lc001 sony 3000 0 bb3 400
我用了3表链接的方法,请问有没有更好的方法
select a.*,b.pact,b.amount
from wu_plan a
join wu_bom b
on b.pla =a.PLA
join (select pla ,min(amount) as minamount from wu_bom group by pla) c
on b.pla =c.pla and b.amount =c.minamount
order by a.pla