可以有以下两种写法: (1).SELECT a_table.a_name, b_table.b_name from a_table,b_table,a_b_table where a_table.a_id=a_b_table.a_id and b_table.b_id=a_b_table.b_id; (2).SELECT a_table.a_name, b_table.b_name FROM (a_b_table INNER JOIN a_table ON a_b_table.a_id = a_table.a_id) INNER JOIN b_table ON a_b_table.b_id = b_table.b_id; 你可以试一下。
SELECT dbo.TableA.a_name + dbo.TableB.b_name AS name FROM dbo.TableC INNER JOIN dbo.TableA ON dbo.TableC.a_id = dbo.TableA.a_id INNER JOIN dbo.TableB ON dbo.TableC.b_id = dbo.TableB.b_id
create table #a(id int,a char(10))insert #a values (1,'x') insert #a values (2,'Y') insert #a values (3,'Z')create table #b(id int,b char(10))insert #b values (1,'A') insert #b values (2,'B') insert #b values (3,'C')create table #C(aid int,bid int)insert #c values (1,1) insert #c values (1,2) insert #c values (2,2) insert #c values (2,3)select a,b from #a left join #c on #a.id=#c.aid left JOIN #b on #b.id=#c.bid结果:a b ---------- ---------- x A x B Y B Y C Z (null) (5 row(s) affected)
select a.a_id as id,a.a_name as name,isnull(c.b_id,'') as bid into #t1 from a left jion c on a.id = c.idselect a.name , b.name from #t1 a, b where a.bid = b.b_id
(1).SELECT a_table.a_name, b_table.b_name
from a_table,b_table,a_b_table
where a_table.a_id=a_b_table.a_id and b_table.b_id=a_b_table.b_id;
(2).SELECT a_table.a_name, b_table.b_name
FROM (a_b_table INNER JOIN a_table ON a_b_table.a_id = a_table.a_id) INNER JOIN b_table ON a_b_table.b_id = b_table.b_id;
你可以试一下。
第二个还没试,不过不知道左连接行不行
FROM dbo.TableC INNER JOIN
dbo.TableA ON dbo.TableC.a_id = dbo.TableA.a_id INNER JOIN
dbo.TableB ON dbo.TableC.b_id = dbo.TableB.b_id
insert #a values (2,'Y')
insert #a values (3,'Z')create table #b(id int,b char(10))insert #b values (1,'A')
insert #b values (2,'B')
insert #b values (3,'C')create table #C(aid int,bid int)insert #c values (1,1)
insert #c values (1,2)
insert #c values (2,2)
insert #c values (2,3)select a,b
from #a left join #c on #a.id=#c.aid
left JOIN #b on #b.id=#c.bid结果:a b
---------- ----------
x A
x B
Y B
Y C
Z (null) (5 row(s) affected)