sql中表的连合

[chenwqi0120]

有如下两个表
table1
id t1
1 100
2 200

table2
id t2
2 200
3 300

不使用临时表的情况下，连接两个表的结果如下：

id t1 t2
1 100 null
2 200 200
3 null 300

[-狙击手-]

select isnull(a.id,b.id) as id ,
a.t1,b.t2
from table1 a full join table2 b on a.id = b.id

[--小F--]

select isnull(a.id,b.id) as id , a.t1,b.t2 
from table1 a full join table2 b on a.id = b.id

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-06-17 21:13:00
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([id] int,[t1] int)
insert [table1]
select 1,100 union all
select 2,200
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([id] int,[t2] int)
insert [table2]
select 2,200 union all
select 3,300
--------------开始查询--------------------------
select isnull(a.id,b.id) as id , a.t1,b.t2 
from table1 a full join table2 b on a.id = b.id
----------------结果----------------------------
/*
id    t1      t2 
1      100    null 
2      200    200 
3      null    300
*/

[feixianxxx]

select isnull(a.id,b.id) as id , 
      a.t1,b.t2 
from table1 a  join table2 b on a.id = b.id

[hyde100]

create table table1
(
id int,
t1 int
)

create table table2
(
id int,
t2 int
)

insert table1
select 1,      100  union
select 2,      200 

insert table2
select 2,      200  union
select 3,      300 

select 
	isnull(table1.id,table2.id) as id,
	t1,
	t2
from table1
	full join table2 on table1.id = table2.id


drop table table1
drop table table2

结果：
1	100	NULL
2	200	200
3	NULL	300

[JonasFeng]

IF OBJECT_ID('LI') IS NOT NULL
   DROP TABLE LI
GO  
 
IF OBJECT_ID('LI2') IS NOT NULL
  DROP TABLE LI2
GO 

CREATE TABLE LI(ID INT ,T1 INT )
INSERT INTO LI(ID,T1)
SELECT 1,100 UNION ALL
SELECT 2,200
GO 

CREATE TABLE LI2(ID INT,T2 INT)
INSERT INTO LI2(ID,T2)
SELECT 2,200 UNION ALL
SELECT 3,300 
GO 

--结果
 SELECT ISNULL(L.ID,T.ID) AS ID ,L.T1,T.T2 
 FROM LI L FULL JOIN  LI2 T 
 ON  L.ID = T.ID 

/*
ID           T1          T2
----------- ----------- -----------
1           100         NULL
2           200         200
3           NULL        300

(3 行受影响)
*/

[chenwqi0120]

谢了，今天试了一下午了

[feixianxxx]

if object_id('[table1]') is not null drop table [table1]
create table [table1]([id] int,[t1] int)
insert [table1]
select 1,100 union all
select 2,200
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([id] int,[t2] int)
insert [table2]
select 2,200 union all
select 3,300

select id=ISNULL(a.id,b.id),t1,t2 from table1 a full join table2 b on a.id=b.id
/*----------------
 1	100	NULL
2	200	200
3	NULL	300
---------------*/