不行啊,而且我要两个表的记录叠加在一起显示啊 table1 id name sex 001 name1 f table2 id name age 002 name2 18结果 id name sex age 001 name1 f 002 name2 18
select id,name,sex,age from table1 a ,table2 b where a.id<>b.id and a.name<>b.name
if object_id('pubs..tb1') is not null drop table tb1 gocreate table tb1 ( id varchar(10), name varchar(10), sex varchar(10) )insert into tb1(id,name,sex) values('001','name1','f') goif object_id('pubs..tb2') is not null drop table tb2 gocreate table tb2 ( id varchar(10), name varchar(10), age int )insert into tb2(id,name,age) values('002','name2',18) goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age from tb1 full join tb2 on tb1.id = tb2.iddrop table tb1,tb2id name sex age ---------- ---------- ---------- ----------- 002 name2 NULL 18 001 name1 f NULL(所影响的行数为 2 行)
if object_id('pubs..tb1') is not null drop table tb1 gocreate table tb1 ( id varchar(10), name varchar(10), sex varchar(10) )insert into tb1(id,name,sex) values('001','name1','f') goif object_id('pubs..tb2') is not null drop table tb2 gocreate table tb2 ( id varchar(10), name varchar(10), age int )insert into tb2(id,name,age) values('002','name2',18) goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age from tb1 full join tb2 on tb1.id = tb2.id order by tb1.iddrop table tb1,tb2 id name sex age ---------- ---------- ---------- ----------- 001 name1 f NULL 002 name2 NULL 18(所影响的行数为 2 行)
还是不行啊,我要的是这样的 table1 id name in_num in_time num 001 name1 20 2006-12-10 30 002 name2 18 2006-12-10 25table2 id name out_num out_time num 002 name2 18 2006-12-12 7 001 name1 15 2006-12-13 15结果 id name in_num in_time out_num out_time num 001 name1 20 2006-12-10 30 002 name2 18 2006-12-10 25 002 name2 18 2006-12-12 7 001 name1 15 2006-12-13 15
table1 id name sex 001 name1 f table2 id name age 002 name2 18结果 id name sex age 001 name1 f 002 name2 18 select isnull(table1.id,table2.id) as id,isnull(table1.name,table2.name) as name,sex,age from table1 full join table 2 on table1.id=table2.id
select isnull(t1.id,t2.id) as id , isnull(t1.name,t2.name) as name , isnull(t1.in_num,null) as in_num,isnull(t1.in_time,null) as in_time,isnull(t2.out_num) as out_num,isnull(t2.out_time,null) as out_time , isnull(t1.num,t2.num) as num from t1 full join t2 on t1.id = t2.id
哦,刚看到你的要求,把连接条件去掉就可以了 on table1.id=table2.id去掉
select isnull(t1.id,t2.id) as id , isnull(t1.name,t2.name) as name , isnull(t1.in_num,null) as in_num,isnull(t1.in_time,null) as in_time,isnull(t2.out_num) as out_num,isnull(t2.out_time,null) as out_time , isnull(t1.num,t2.num) as num from t1 full join t2 on t1.id = t2.id and t1.name=t2.name 凑巧你这里的id和name都相同,如果不同,就会更体现效果
table1
id name sex
001 name1 f
table2
id name age
002 name2 18结果
id name sex age
001 name1 f
002 name2 18
drop table tb1
gocreate table tb1
(
id varchar(10),
name varchar(10),
sex varchar(10)
)insert into tb1(id,name,sex) values('001','name1','f')
goif object_id('pubs..tb2') is not null
drop table tb2
gocreate table tb2
(
id varchar(10),
name varchar(10),
age int
)insert into tb2(id,name,age) values('002','name2',18)
goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age
from tb1
full join tb2
on tb1.id = tb2.iddrop table tb1,tb2id name sex age
---------- ---------- ---------- -----------
002 name2 NULL 18
001 name1 f NULL(所影响的行数为 2 行)
drop table tb1
gocreate table tb1
(
id varchar(10),
name varchar(10),
sex varchar(10)
)insert into tb1(id,name,sex) values('001','name1','f')
goif object_id('pubs..tb2') is not null
drop table tb2
gocreate table tb2
(
id varchar(10),
name varchar(10),
age int
)insert into tb2(id,name,age) values('002','name2',18)
goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age
from tb1
full join tb2
on tb1.id = tb2.id
order by tb1.iddrop table tb1,tb2
id name sex age
---------- ---------- ---------- -----------
001 name1 f NULL
002 name2 NULL 18(所影响的行数为 2 行)
table1
id name in_num in_time num
001 name1 20 2006-12-10 30
002 name2 18 2006-12-10 25table2
id name out_num out_time num
002 name2 18 2006-12-12 7
001 name1 15 2006-12-13 15结果
id name in_num in_time out_num out_time num
001 name1 20 2006-12-10 30
002 name2 18 2006-12-10 25
002 name2 18 2006-12-12 7
001 name1 15 2006-12-13 15
id name sex
001 name1 f
table2
id name age
002 name2 18结果
id name sex age
001 name1 f
002 name2 18
select isnull(table1.id,table2.id) as id,isnull(table1.name,table2.name) as name,sex,age from table1
full join
table 2
on table1.id=table2.id
from t1
full join t2
on t1.id = t2.id
on table1.id=table2.id去掉
from t1
full join t2
on t1.id = t2.id and t1.name=t2.name
凑巧你这里的id和name都相同,如果不同,就会更体现效果