多表关联的sql语句,该如何处理

多表关联的sql语句
表table1
newcode iname
20071105 t2
20076789 t3
20071882 t4
表table2
newcode iiname
20071105 t5
20071105 t6

表table3
newcode iiiname
20071105 t7
20071105 t8
20071105 t9

表tabe1的newcode是唯一的，没有重复，其他两个表的newcode与table1对应，可以重复（例如表table2有两个newcode为20071105的数据），现在我要得到如下结果
newcode iname iiname iiiname
20071105 t2 t5 t7
当newcode=20071105,从表table2中找newcode为20071105的所有值，但只取第一条
table3同上。

------解决方案--------------------

SQL code

select newcode,iname,max(iiname),max(iiiname)
from (select t1.newcode,iname,iiname,iiiname 
     from table1 t1,table2 t2,table3 t3 
     where t1.newcode=t2.newcode and t1.newcode=t3.newcode) t
group by newcode,iname;

select newcode,iname,min(iiname),min(iiiname)
from (select t1.newcode,iname,iiname,iiiname 
     from table1 t1,table2 t2,table3 t3 
     where t1.newcode=t2.newcode and t1.newcode=t3.newcode) t
group by newcode,iname;

------解决方案--------------------

SQL code

select t1.newcode, t1.iname, t2.iiname, t3.iiiname
from table1 t1 join (select newcode, min(iiname) from table2 group by newcode) t2 on t1.newcode=t2.newcode
    join (select newcode, min(iiiname) from table3 group by newcode) t3 on t1.newcode=t3.newcode

多表关联的sql语句,该如何处理

相关推荐