34,575
社区成员
发帖
与我相关
我的任务
分享
table1
字段:cmpnt_id,cmpnt_name
Table2
字段:aaa,bbb
aaa和bbb两个字段里都是第一个表中cmpnt_name字段里的内容
也就是说可以有类似这两个查询语句的成立:
select cmpnt_id,cmpnt_name from table1,table2 where table1.cmpnt_name = table2.aaa;
select cmpnt_id,cmpnt_name from table1,table2 where table1.cmpnt_name = table2.bbb;
如何写sql得到这样一个查询结果(cmpnt_id1,cmpnt_name1,cmpnt_id2,cmpnt_name2),也就是说以table2为基准,得到table2中每条记录中两个不同cmpnt的id和name。谢谢!!
select
A.id as cmpnt_id1,
A.name as cmpnt_name1,
B.id as cmpnt_id2,
B.name as cmpnt_name2
from
(
select t1.id, t1.name,t2.bbbb
from table1 as t1
inner join tabel2 as t2 on t1.name=t2.aaaa
) as A --此处须将bbbb查询出来,用于与外层连接
inner join table1 as B on A.bbbb=B.name
select T1.cmpnt_id 'cmpnt_id1',T1.cmpnt_name 'cmpnt_name1',T2.cmpnt_id 'cmpnt_id2',T2.cmpnt_name 'cmpnt_name2'
from Table2 T0
left join table1 T1 on T0.aaa=T1.cmpnt_name
left join table2 T2 on T0.bbb=T2.cmpnt_name
select b.cmpnt_id as cmpnt_id1,a.aaa as cmpnt_name1,c.cmpnt_id2 as cmpnt_id2,a.bbb as cmpnt_name2
from table2 a
join table1 b on a.aaa=b.cmpnt_name
join table1 c on a.bbb=c.cmpnt_name
select b.cmpnt_id 'cmpnt_id1',
b.cmpnt_name 'cmpnt_name1',
c.cmpnt_id 'cmpnt_id2',
c.cmpnt_name 'cmpnt_name2',
from Table2 a
left join table1 b on a.aaa=b.cmpnt_name
left join table1 c on a.bbb=b.cmpnt_name