如何实现左右连接

[frankie_24]

有个问题请教
表A 字段ID
表B 字段ID

A的记录为
1
2
3
4
B的记录为
0
2

要求两个表连接起来以后显示为
0
1
2
3
4
不知道我说的清楚不清楚.也就是说,表A,B的都要显示出来.
谢谢了

[liangCK]

[Quote=引用 6 楼 happyflystone 的回复:]
引用 4 楼 xiaomeixiang 的回复:
select id from a
union
select id from b


强,
[/Quote]

[Limpire]

--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID int)
insert into #A
select 1 union all
select 2 union all
select 3 union all
select 4
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (ID int)
insert into #B
select 0 union all
select 2

select ID from #A
UNION
select ID from #B

/*
ID
-----------
0
1
2
3
4
*/

[dobear_0922]

select id from a 
union 
select id from b

[frankie_24]

呵呵,可能是我解释的不清楚.不是这么简单.
我的问题..谢谢大家了..我心思心思怎么解释昂..

[-狙击手-]

不对吧,楼主要的是联结,

所以我和小梁得分

yeah

[wpeng8218]

declare @t table(id int)

insert into @t values(1)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)

declare @b table(id int)

insert into @b values(0)
insert into @b values(2)


select * from (
	select * from @t
	union all
	select * from @b where id not in (select id from @t) 
) t 
order by id

[wpeng8218]

declare @t table(id int)

insert into @t values(1)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)

declare @b table(id int)

insert into @b values(0)
insert into @b values(2)


select isnull(a.id,b.id) id from @t a full join @b b on a.id=b.id order by id

[ojuju10]

--用union
declare @t table(id int)

insert into @t values(1)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)

declare @b table(id int)

insert into @b values(0)
insert into @b values(2)


select * from @t
union
select * from @b

[frankie_24]

呵呵..是我说的不清楚,不是这么简单
我在屡屡..有点乱

[frankie_24]

我重新说
A表 字段Date,Hour(正常工作时数)
B表 字段Date,OverTime(加班小时数)
A表记录
2008/01/02 8
2008/01/03 9
2008/01/04 8

B表记录
2008/01/01 2
2008/01/02 3
首先,A和B表一定要连接起来
比如
Select A.Date ,Hour,OverTime From A
Left Join B on A.Date=B.Date
这样显示的记录为
Date Hour OverTime
2008/01/02 8 3
2008/01/03 9 null
2008/01/04 8 null

但是,因为加班那天可以不用记录正常的小时数
也就是说2008/01/01那天只有加班小时数
但是连接以后就不会出来.我想要的是

Date Hour OverTime
2008/01/01 null 2
2008/01/02 8 3
2008/01/03 9 null
2008/01/04 8 null


不知道我说清楚了没有?
谢谢伙计们了..

[frankie_24]

刚才人那么多,现在人呢?呵呵,我重新说了我的问题了,刚才没有说明白,大家看看啊 ..谢谢了

[xiaomeixiang]

select isnull(a.date,b.date) date,a.hour,b.overtime
from a full join b
on a.date=b.date

[elvis_gao]

declare @ta table(id int,name int)
insert @ta select
1,3 union select
2,4  union select
3,5  union select
4,6

declare @tb table(id int,name int)
insert @tb select
0,7 union select
2,8


select isnull(a.id,b.id),a.name, b.name
from @ta a full join @tb b on a.id = b.id 

[elvis_gao]

/* 
           name        name
----------- ----------- -----------
1           3           NULL
2           4           8
3           5           NULL
4           6           NULL
0           NULL        7
*/

[wangdehao]

select a.*,b.OverTime
from A
full join
B
on A.Date=b.Date

[xiaomeixiang]

create table a(date datetime,hour int)
insert a
select '2008/01/02',  8 
union all select '2008/01/03',  9 
union all select '2008/01/04',  8 

create table b(date datetime,overtime int)
insert b
select '2008/01/01',  2 
union all select '2008/01/02',  3 


select isnull(a.date,b.date) date,a.hour,b.overtime
from a full join b
on a.date=b.date
order by isnull(a.date,b.date)

drop table a,b

[xiaomeixiang]

哈，三找怎么有空上CSDN了， :D

[wzjpsq]

full join

[frankie_24]

谢谢大家啊,我刚才也是用Full把我自己的例子实现了,但是放到正式的表中就不行,
非常感谢大家,给我了很多启发...我在看看...

[frankie_24]

谢谢大家啊,一样的问题,没法给分 了,就这样分了昂...