一个多表查询的多种方法

--(1)偶的方法 

原贴地址：

http://topic.csdn.net/u/20091113/20/def54614-3035-4f7a-8958-4d77107b0515.html?72858

create table tableA
(
  ID int,
  name varchar(20)
)
insert into tableA select 1,'张三'
insert into tableA select 2,'李四'
insert into tableA select 3,'王五'
insert into tableA select 7,'赵六'
insert into tableA select 8,'孙七'


create table tableB
(
    ID int,
  area varchar(20)
)
insert into tableB select 1,'北京'
insert into tableB select 3,'上海'
insert into tableB select 4,'天津'
insert into tableB select 5,'青岛'
insert into tableB select 9,'成都'

create table tableC
(
  ID int,
  career varchar(20)
)
insert into tableC select 2,'学生'
insert into tableC select 3,'工人'
insert into tableC select 5,'教师'
insert into tableC select 6,'警察'
insert into tableC select 7,'医生'

;with hgo as
(
select p.number AS ID,A.name from tableA A
right join
(
select number from master..spt_values where type='p'
) p on p.number=A.ID where number<=
(select max(ID)
from
(
select A.ID from tableA A
union all
select B.ID from tableB B
union all
select C.ID from tableC C
) tt) and number>0
)
select h.*,B.area,C.career from hgo h left join tableB B
on h.ID=B.ID left join tableC C
on C.ID=h.ID
ID          name                 area                 career
----------- -------------------- -------------------- --------------------
1           张三                   北京                   NULL
2           李四                   NULL                 学生
3           王五                   上海                   工人
4           NULL                 天津                   NULL
5           NULL                 青岛                   教师
6           NULL                 NULL                 警察
7           赵六                   NULL                 医生
8           孙七                   NULL                 NULL
9           NULL                 成都                   NULL

--（2）下面是不逆的方法

 

 

select ID=isnull(TA.ID,isnull(TB.ID,TC.ID)),
    [name]=max(name),
    [area]=max(area),
    [career]=max(career)
from [TA] full join TB on TA.ID=TB.ID  full join TC ON TB.ID=TC.ID
group by isnull(TA.ID,isnull(TB.ID,TC.ID))