A,B两表查询

[dlforall]

A(CITY1,CITY2)
SHANGHAI BEIJIN
BEIJIN SHANGHAI


B CITYENAME CITYCNAME
SHANGHAI 上海
BEIJIN 北京


要求结果
SHANGHAI 上海 BEIJIN 北京
BEIJIN 北京 SHANGHAI 上海

[hao1hao2hao3]

if object_id('a')>0
	drop table a
create table a
(
	city1 varchar(20),
	city2 varchar(20)
)
if object_id('b')>0
	drop table b
create table b
(
	cityename varchar(20),
	citycname nvarchar(20)
)

insert into a 
select 'SHANGHAI', 'BEIJIN'
union all
select 'BEIJIN', 'SHANGHAI'

insert into b
select 'SHANGHAI', '上海'
union all
select 'BEIJIN', '北京'

select a.city1,b.citycname,a.city2,c.citycname
from a left join b on a.city1 = b.cityename 
left join b as c on a.city2 = c.cityename

结果
SHANGHAI	上海	BEIJIN	北京
BEIJIN	北京	SHANGHAI	上海

[hao1hao2hao3]

纠正1楼的，
left joini b as c on 多了一个i
应该是
left join b as c on

[「已注销」]

试试，没测试

select a.CITY1,CITYCNAME1 =(select b.CITYCNAME from B where CITYENAME =a.CITY1),
a.CITY2,CITYCNAME2 =(select b.CITYCNAME from B where CITYENAME =a.CITY2)
from A

[华夏小卒]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
go
create table [A] (CITY1 varchar(8),CITY2 varchar(8))
insert into [A]
select 'SHANGHAI','BEIJIN' union all
select 'BEIJIN','SHANGHAI'
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
go 
create table [B] (CITYENAME varchar(8),CITYCNAME varchar(4))
insert into [B]
select 'SHANGHAI','上海' union all
select 'BEIJIN','北京'

 select a.city1,c.CITYCNAME,a.city2,d.CITYCNAME
from a
left join b c on a.city1=c.cityename
left join b d on a.city2=d.cityename

city1    CITYCNAME city2    CITYCNAME
-------- --------- -------- ---------
SHANGHAI 上海        BEIJIN   北京
BEIJIN   北京        SHANGHAI 上海

(2 行受影响)

[「已注销」]

select a.CITY1,CITYCNAME1 =(select b.CITYCNAME from B where CITYENAME =a.CITY1),
	   a.CITY2,CITYCNAME2 =(select b.CITYCNAME  from B where CITYENAME =a.CITY2)
from A

CITY1                CITYCNAME1           CITY2                CITYCNAME2
-------------------- -------------------- -------------------- --------------------
SHANGHAI             上海                   BEIJIN               北京
BEIJIN               北京                   SHANGHAI             上海

(2 row(s) affected)

[dawugui]

create table [A] (CITY1 varchar(8),CITY2 varchar(8))
insert into [A]
select 'SHANGHAI','BEIJIN' union all
select 'BEIJIN','SHANGHAI'
create table [B] (CITYENAME varchar(8),CITYCNAME varchar(4))
insert into [B]
select 'SHANGHAI','上海' union all
select 'BEIJIN','北京'

select a.CITY1,
       (select CITYCNAME from b where b.CITYENAME = a.CITY1),
       a.CITY2,
       (select CITYCNAME from b where b.CITYENAME = a.CITY2)
from a

drop table a , b

/*
CITY1         CITY2         
-------- ---- -------- ---- 
SHANGHAI 上海   BEIJIN   北京
BEIJIN   北京   SHANGHAI 上海

（所影响的行数为 2 行）
*/

[无涯大者]

复合查询

select a.city1,c.CITYCNAME,a.city2,d.CITYCNAME
from a
left join b c on a.city1=c.cityename
left join b d on a.city2=d.cityename

[fpzgm]

select a.city1,
       c.CITYCNAME,
       a.city2,
       d.CITYCNAME
from a 
left join b c on a.city1=c.cityename
left join b d on a.city2=d.cityename

[pengpeng409]

学习。。。

[ws_hgo]

if object_id('[A]') is not null drop table [A]
go
create table [A] (CITY1 varchar(8),CITY2 varchar(8))
insert into [A]
select 'SHANGHAI','BEIJIN' union all
select 'BEIJIN','SHANGHAI'

if object_id('[B]') is not null drop table [B]
go 
create table [B] (CITYENAME varchar(8),CITYCNAME varchar(4))
insert into [B]
select 'SHANGHAI','上海' union all
select 'BEIJIN','北京'

select a.city1,c.CITYCNAME,a.city2,d.CITYCNAME
from a
left join b c 
on a.city1=c.cityename
left join b d 
on a.city2=d.cityename

city1    CITYCNAME city2    CITYCNAME
-------- --------- -------- ---------
SHANGHAI 上海        BEIJIN   北京
BEIJIN   北京        SHANGHAI 上海

(2 行受影响)

[幸运的意外]

分别按照city1，city2为连接条件，两次链接B表就可以了。
select A.city1,B.citycname, A.city2, C.citycname
from A left join B on city1 = B.cityename1
left joini B C on city2 = C.cityename

[cnwolfwind]

select a.city1,b1.citycname,a.city2,b2.citycname from a,b b1,b b2 where a.city1=b1.cityename and a.city2=b2.cityename

SHANGHAI  	上海      	BEIJIN    	北京      
BEIJIN    	北京      	SHANGHAI  	上海      

sql 2000下测试成功

[pengpeng409]

select a.city1,b.citycname,a.city2,c.citycname
from a left join b on a.city1 = b.cityename
left joini b as c on a.city2 = c.cityename

[ajige]

联合查询 就好了~~

[ai_li7758521]

CREATE TABLE [A] (CITY1 varchar(8),CITY2 varchar(8))
INSERT INTO [A]
SELECT 'SHANGHAI','BEIJIN' UNION ALL
SELECT 'BEIJIN','SHANGHAI'


CREATE TABLE [B] (CITYENAME varchar(8),CITYCNAME varchar(4))
INSERT INTO [B]
SELECT 'SHANGHAI','上海' UNION ALL
SELECT 'BEIJIN','北京'

SELECT A.CITY1,B.CITYCNAME,A.CITY2,C.CITYCNAME
FROM A
LEFT JOIN B ON A.CITY1=CITYENAME
LEFT JOIN B C ON A.CITY2=C.CITYENAME

CITY1    CITYCNAME CITY2    CITYCNAME
-------- --------- -------- ---------
SHANGHAI 上海        BEIJIN   北京
BEIJIN   北京        SHANGHAI 上海

(2 行受影响)

[mayala520yy]

学习中。。。。

[Roni]

众位高手说的都对。

[喜-喜]

[Quote=引用 19 楼 geng_ping_ping 的回复:]
众位高手说的都对。
[/Quote]

赞同...

[luoyukang]

select a.city1,a.citycname,a.city2,b.citycname
from (select * from a,b where a.city1 = b.cityename) a,b
where a.city2=b.cityename

[chen8410]

select a.city1,b1.citycname,a.city2,b2.citycname
from a
left join b b1 on
a.city1=b1.cityename
left join b b2 on
a.city2=b2.cityename