日期:2014-05-18  浏览次数:20743 次

多表关联的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