select isnull(n.name ,d.name ) as name,manager,Director from manager n inner join director d on n.name =d.name
估计你们还是没有看懂我的意思: 假设table1: name manager BIDC a BIDC b BE j BE i table 2: name director BIDC e BIDC f BIDC g BIDC h BE k 得出table3: name manager director BIDC a e BIDC b f BIDC g BIDC h BE j k BE i
---测试数据--- if object_id('[table1]') is not null drop table [table1] go create table [table1]([name] varchar(4),[manager] varchar(1)) insert [table1] select 'BIDC','a' union all select 'BIDC','b' union all select 'BE','j' union all select 'BE','i' go if object_id('[table2]') is not null drop table [table2] go create table [table2]([name] varchar(4),[director] varchar(1)) insert [table2] select 'BIDC','e' union all select 'BIDC','f' union all select 'BIDC','g' union all select 'BIDC','h' union all select 'BE','k' go---查询--- select isnull(t2.name,t1.name) as name, isnull(t1.manager,'') as manager, isnull(t2.director,'') as director from (select *,rn=row_number() over(partition by name order by getdate()) from table2) t2 full join (select *,rn=row_number() over(partition by name order by getdate()) from table1) t1 on t1.name=t2.name and t1.rn=t2.rn---结果--- name manager director ---- ------- -------- BE j k BE i BIDC a e BIDC b f BIDC g BIDC h(6 行受影响)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-05-19 22:03:20 -- Verstion: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[table1] if object_id('[table1]') is not null drop table [table1] go create table [table1]([name] varchar(4),[manager] varchar(1)) insert [table1] select 'BIDC','a' union all select 'BIDC','b' union all select 'BE','j' union all select 'BE','i' --> 测试数据:[table2] if object_id('[table2]') is not null drop table [table2] go create table [table2]([name] varchar(4),[director] varchar(1)) insert [table2] select 'BIDC','e' union all select 'BIDC','f' union all select 'BIDC','g' union all select 'BIDC','h' union all select 'BE','k' --------------开始查询-------------------------- select isnull(b.name,a.name) as name, isnull(a.manager,'') as manager, isnull(b.director,'') as director from (select *,px=row_number() over(partition by name order by getdate()) from table1)a full join (select *,px=row_number() over(partition by name order by getdate()) from table2)b on a.name=b.name and a.px=b.px order by name desc ----------------结果---------------------------- /* name manager director ---- ------- -------- BIDC a e BIDC b f BIDC g BIDC h BE j k BE i (6 行受影响) */
select isnull(n.name ,d.name ) as name,manager,Director from manager n inner join director d on n.name =d.name
假设table1:
name manager
BIDC a
BIDC b
BE j
BE i
table 2:
name director
BIDC e
BIDC f
BIDC g
BIDC h
BE k
得出table3:
name manager director
BIDC a e
BIDC b f
BIDC g
BIDC h
BE j k
BE i
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([name] varchar(4),[manager] varchar(1))
insert [table1]
select 'BIDC','a' union all
select 'BIDC','b' union all
select 'BE','j' union all
select 'BE','i'
go
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([name] varchar(4),[director] varchar(1))
insert [table2]
select 'BIDC','e' union all
select 'BIDC','f' union all
select 'BIDC','g' union all
select 'BIDC','h' union all
select 'BE','k'
go---查询---
select
isnull(t2.name,t1.name) as name,
isnull(t1.manager,'') as manager,
isnull(t2.director,'') as director
from
(select *,rn=row_number() over(partition by name order by getdate()) from table2) t2
full join
(select *,rn=row_number() over(partition by name order by getdate()) from table1) t1
on
t1.name=t2.name and t1.rn=t2.rn---结果---
name manager director
---- ------- --------
BE j k
BE i
BIDC a e
BIDC b f
BIDC g
BIDC h(6 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-05-19 22:03:20
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([name] varchar(4),[manager] varchar(1))
insert [table1]
select 'BIDC','a' union all
select 'BIDC','b' union all
select 'BE','j' union all
select 'BE','i'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([name] varchar(4),[director] varchar(1))
insert [table2]
select 'BIDC','e' union all
select 'BIDC','f' union all
select 'BIDC','g' union all
select 'BIDC','h' union all
select 'BE','k'
--------------开始查询--------------------------
select
isnull(b.name,a.name) as name,
isnull(a.manager,'') as manager,
isnull(b.director,'') as director
from
(select *,px=row_number() over(partition by name order by getdate()) from table1)a
full join
(select *,px=row_number() over(partition by name order by getdate()) from table2)b
on
a.name=b.name and a.px=b.px
order by
name desc
----------------结果----------------------------
/* name manager director
---- ------- --------
BIDC a e
BIDC b f
BIDC g
BIDC h
BE j k
BE i (6 行受影响)
*/