select isnull(a.id,b.id) from ta a join tb b on a.id = b.id
select id from a union select id from b
declare @ta table(id int) insert @ta select 1 union select 2 union select 3 union select 4declare @tb table(id int) insert @tb select 0 union select 2 select isnull(a.id,b.id) from @ta a full join @tb b on a.id = b.id/*
----------- 0 2 4 3 1(所影响的行数为 5 行) */
select ID from a union select ID from b
select * from A UNION select * from B
--> 测试数据: #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 2select ID from #A UNION select ID from #B/* ID ----------- 0 1 2 3 4 */
select id from a union select id from b
不对吧,楼主要的是联结,所以我和小梁得分yeah
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
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
--用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
我重新说 A表 字段Date,Hour(正常工作时数) B表 字段Date,OverTime(加班小时数) A表记录 2008/01/02 8 2008/01/03 9 2008/01/04 8B表记录 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 不知道我说清楚了没有? 谢谢伙计们了..
select isnull(a.date,b.date) date,a.hour,b.overtime from a full join b on a.date=b.date
declare @ta table(id int,name int) insert @ta select 1,3 union select 2,4 union select 3,5 union select 4,6declare @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
select a.*,b.OverTime from A full join B on A.Date=b.Date
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
from ta a join tb b on a.id = b.id
union
select id from b
insert @ta select
1 union select
2 union select
3 union select
4declare @tb table(id int)
insert @tb select
0 union select
2
select isnull(a.id,b.id)
from @ta a full join @tb b on a.id = b.id/*
-----------
0
2
4
3
1(所影响的行数为 5 行)
*/
union
select ID from b
UNION
select * from B
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 2select ID from #A
UNION
select ID from #B/*
ID
-----------
0
1
2
3
4
*/
union
select id from b
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
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
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
A表 字段Date,Hour(正常工作时数)
B表 字段Date,OverTime(加班小时数)
A表记录
2008/01/02 8
2008/01/03 9
2008/01/04 8B表记录
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
不知道我说清楚了没有?
谢谢伙计们了..
select isnull(a.date,b.date) date,a.hour,b.overtime
from a full join b
on a.date=b.date
declare @ta table(id int,name int)
insert @ta select
1,3 union select
2,4 union select
3,5 union select
4,6declare @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
name name
----------- ----------- -----------
1 3 NULL
2 4 8
3 5 NULL
4 6 NULL
0 NULL 7
*/
from A
full join
B
on A.Date=b.Date
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
非常感谢大家,给我了很多启发...我在看看...