sql2005 多表联合查询，实在没办法了

[一休乐乐哥]

我试过left join和right join，都不行，是在没办法了，哪个高手帮一下忙，谢谢！！

表 Tdep1
CID CNAME CCODE AGE
2 李华 002 19
3 小明 003 20
表 Tdep2
CID CNAME CCODE SEX
1 小刚 001 男
2 李华 002 男
4 小敏 004 女

想查成
CID CNAME CCODE AGE SEX
1 小刚 001 男
2 李华 002 19 男
3 小明 003 20
4 小敏 004 女

[guguda2008]

FULL JOIN。。。。

[--小F--]

select 
  a.CID,a.CNAME,a.CCODE,isnull(a.AGE,'') as AGE,isnull(b.SEX,'') as SEX 
from 
  Tdep1 a
full join
  Tdep2 b 
on 
  a.CID=b.CID

[feixianxxx]

select 
cid=ISNULL(a.cid,b.cid),
a.cname,
ccode=ISNULL(a.ccode,b.ccode),
age=(case when age IS null then ' ' else LTRIM(age) end ),
sex =(case when sex IS null then ' ' else sex  end )
from tdep1 a full join tdep2 b 
on a.cid=b.cid

[feixianxxx]

select 
cid=ISNULL(a.cid,b.cid),
cname=ISNULL(a.cname,b.cname)
ccode=ISNULL(a.ccode,b.ccode),
age=(case when age IS null then ' ' else LTRIM(age) end ),
sex =(case when sex IS null then ' ' else sex  end )
from tdep1 a full join tdep2 b 
on a.cid=b.cid

[SQL77]

SELECT 
ISNULL(T1.CID,T2.CID)CID,
ISNULL(T1.CNAME,T2.CNAME)CNAME,
ISNULL(T1.CCODE,T2.CCODE)CCODE,
T1.AGE,
T2.SEX
FROM Tdep1 T1 LEFT JOIN Tdep2 T2 ON T1.CID=T2.CID

[feixianxxx]

select 
cid=ISNULL(a.cid,b.cid),
cname=ISNULL(a.cname,b.cname),
ccode=ISNULL(a.ccode,b.ccode),
age=(case when age IS null then ' ' else LTRIM(age) end ),
sex =(case when sex IS null then ' ' else sex  end )
from tdep1 a full join tdep2 b 
on a.cid=b.cid

[guguda2008]

SELECT ISNULL(T1.CID,T2.CID),ISNULL(T1.CNAME,T2.CNAME),ISNULL(T1.CCODE,T2.CCODE),AGE,SEX
FROM Tdep1 T1
FULL JOIN Tdep2 T2 ON T1. CID    =T2.CID

[黄_瓜]

select a.CID,a.CNAME,a.CCODE,
isnull(a.AGE,'') as AGE,
isnull(b.SEX,'') as SEX 
from   Tdep1 a
full join  Tdep2 b 
on   a.CID=b.CID

[guguda2008]

还好没写代码直接抢SF了

[华夏小卒]

declare @Tdep1 table(CID int,   CNAME varchar(12), CCODE varchar(12),  AGE int)
insert @Tdep1 select
2   ,   '李华'  ,  '002'   , 19 union all select
3    ,  '小明'  ,  '003'  ,  20 
declare @Tdep2 table(CID int,   CNAME varchar(12), CCODE varchar(12),  sex varchar(12))

insert @Tdep2 select
1  ,    '小刚' ,   '001'   , '男' union all select
2  ,    '李华' ,   '002'  ,  '男' union all select
4  ,    '小敏'  ,  '004'   , '女' 

select cid=isnull(a.cid,b.cid),cname=isnull(a.cname,b.cname),ccode=isnull(a.ccode,b.ccode),a.AGE,b.sex
from @tdep1 a
full join @tdep2 b
on a.cid=b.cid
order by cid

cid         cname        ccode        AGE         sex
----------- ------------ ------------ ----------- ------------
1           小刚           001          NULL        男
2           李华           002          19          男
3           小明           003          20          NULL
4           小敏           004          NULL        女

(4 行受影响)

[SQL77]

SELECT 
ISNULL(T1.CID,T2.CID)CID,
ISNULL(T1.CNAME,T2.CNAME)CNAME,
ISNULL(T1.CCODE,T2.CCODE)CCODE,
T1.AGE,
T2.SEX
FROM Tdep1 T1 FULL JOIN Tdep2 T2 ON T1.CID=T2.CID

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-11 16:03:40
----------------------------------------------------------------
--> 测试数据：[Tdep1]
if object_id('[Tdep1]') is not null drop table [Tdep1]
go
create table [Tdep1]([CID] int,[CNAME] varchar(4),[CCODE] varchar(3),[AGE] int)
insert [Tdep1]
select 2,'李华','002',19 union all
select 3,'小明','003',20
--> 测试数据：[Tdep2]
if object_id('[Tdep2]') is not null drop table [Tdep2]
go
create table [Tdep2]([CID] int,[CNAME] varchar(4),[CCODE] varchar(3),[SEX] varchar(2))
insert [Tdep2]
select 1,'小刚','001','男' union all
select 2,'李华','002','男' union all
select 4,'小敏','004','女'
--------------开始查询--------------------------
select 
  CID=ISNULL(a.cid,b.cid),
  cname=ISNULL(a.cname,b.cname),
  ccode=ISNULL(a.ccode,b.ccode),
  isnull(a.AGE,'') as AGE,
  isnull(b.SEX,'') as SEX 
from 
  Tdep1 a
full join
  Tdep2 b 
on 
  a.CID=b.CID
order by
  cid
  
----------------结果----------------------------
/*CID         cname ccode AGE         SEX  
----------- ----- ----- ----------- ---- 
1           小刚    001   0           男
2           李华    002   19          男
3           小明    003   20          
4           小敏    004   0           女

（所影响的行数为 4 行）

*/

[--小F--]

为了抢分搞错了 哎 网速也不行了

[一休乐乐哥]

啊，给谁好啊！！！找到我需要的了

[一休乐乐哥]

分数都分给你们了，后面的朋友不好意思啊，不够分了

[jiayinuser]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-11 16:03:40
----------------------------------------------------------------
--> 测试数据：[Tdep1]
if object_id('[Tdep1]') is not null drop table [Tdep1]
go
create table [Tdep1]([CID] int,[CNAME] varchar(4),[CCODE] varchar(3),[AGE] int)
insert [Tdep1]
select 2,'李华','002',19 union all
select 3,'小明','003',20
--> 测试数据：[Tdep2]
if object_id('[Tdep2]') is not null drop table [Tdep2]
go
create table [Tdep2]([CID] int,[CNAME] varchar(4),[CCODE] varchar(3),[SEX] varchar(2))
insert [Tdep2]
select 1,'小刚','001','男' union all
select 2,'李华','002','男' union all
select 4,'小敏','004','女'
--------------开始查询--------------------------
select
CID=ISNULL(a.cid,b.cid),
cname=ISNULL(a.cname,b.cname),
ccode=ISNULL(a.ccode,b.ccode),
isnull(a.AGE,'') as AGE,
isnull(b.SEX,'') as SEX
from
Tdep1 a
full join
Tdep2 b
on
a.CID=b.CID
order by
cid

----------------结果----------------------------
/*CID cname ccode AGE SEX
----------- ----- ----- ----------- ----
1 小刚 001 0 男
2 李华 002 19 男
3 小明 003 20
4 小敏 004 0 女

（所影响的行数为 4 行）

*/