求助一个SQL语句的写法

alex8315 2014-06-19 06:09:18
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。谢谢!!

...全文
179 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
taotao21a 2014-06-20
  • 打赏
  • 举报
回复

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
netajax1 2014-06-20
  • 打赏
  • 举报
回复

 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
习惯性蹭分 2014-06-19
  • 打赏
  • 举报
回复

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
唐诗三百首 2014-06-19
  • 打赏
  • 举报
回复

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
guostong 2014-06-19
  • 打赏
  • 举报
回复
select distinct t11.cmpnt_id, t2.aaa, t12.cmpnt_id, t2.bbb from table1 T11, table1 T12, table2 T2 where t11.cmpnt_name = t2.aaa and t12.cmpnt_name = t2.bbb

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧