有一条数据
id hotelname areacity areaarea
1 sss 31 45其中 areacity areaarea 对应的 是另一个表的 ID
如
id city
31 aaa
45 bbb我现在要一句SQL语句 把 sss aaa bbb 都取出来 可以吗
id hotelname areacity areaarea
1 sss 31 45其中 areacity areaarea 对应的 是另一个表的 ID
如
id city
31 aaa
45 bbb我现在要一句SQL语句 把 sss aaa bbb 都取出来 可以吗
A.hotelname,
B.city As areacity,
C.city As areaarea
From
表1 A
Left Join
表2 B
On A.areacity = B.id
Left Join
表2 C
On A.areaarea = C.id
FROM 表1 A LEFT JOIN 表2 B ON A.areacity=B.ID
LEFT JOIN 表2 C ON A.areaarea=C.ID
(select city from 表2 where id=a.areacity) areacity,
(select city from 表2 where id=a.areaarea ) areaarea
from 表1 a
drop table tba
go
create table tba(id int,hotelname varchar(10),areacity int,areaarea int)
insert into tba(id,hotelname,areacity,areaarea) values(1, 'sss', 31, 45)
goif object_id('pubs..tbb') is not null
drop table tbb
go
create table tbb(id int,city varchar(10))
insert into tbb(id,city) values(31, 'aaa')
insert into tbb(id,city) values(45, 'bbb')
goselect t1.hotelname,t2.city,t3.city from
(select hotelname from tba ) t1,
(select b.city from tba a, tbb b where a.areacity = b.id) t2,
(select b.city from tba a, tbb b where a.areaarea = b.id) t3drop table tba,tbb/*
hotelname city city
---------- ---------- ----------
sss aaa bbb(所影响的行数为 1 行)
*/
select a.id,a.hotelname,b.city as areacity,c.city as areaarea
from 表a join 另一个表 on a.areacity=另一个表.id
join 另一个表 as c on a.areaarea=c.id
FROM Table2
WHERE hotelname = b.ID) AS A1,
(SELECT City FROM Table2
WHERE areacity= b.ID) AS A2,
(SELECT City FROM Table2
WHERE areaarea = b.ID) AS A3,
FROM Table1 b
insert into #1 values(1,'sss',31,45)create table #2 (id int ,city varchar(10))
insert into #2 values(31,'aaa')
insert into #2 values(45,'bbb')select hotelname,b.city as areacity,c.city as areaarea
from #1 a join #2 b on a.areacity=b.id join #2 c
on a.areaarea=c.idhotelname areacity areaarea
---------- ---------- ----------
sss aaa bbb(所影响的行数为 1 行)
----------------------------------------
sssaaabbb(所影响的行数为 1 行)