关于两表合并的问题

[赖志雄]

本人是个新手，所以有个问题想问问
目前有表1
number name
1 A
2 B
3 C

表二
number code
1 D
1 E
2 D
3 E

现在想合并成表3
number name code
1 A D
1 null E
2 B D
3 C E

麻烦各位大神提供一下帮助，先谢谢各位了！

[赖志雄]

麻烦各位帮忙看一下， 谢谢哈

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([number] int,[name] nvarchar(21))
Insert #T1
select 1,N'A' union all
select 2,N'B' union all
select 3,N'C'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([number] int,[code] nvarchar(21))
Insert #T2
select 1,N'D' union all
select 1,N'E' union all
select 2,N'D' union all
select 3,N'E'
Go
--测试数据结束
SELECT ISNULL(t1.number,t2.number) AS number,
t1.Name,t2.code
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY number ORDER BY name) rn
    FROM #T1
) t1
    FULL JOIN
    (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY number ORDER BY code) rn
        FROM #T2
    ) t2
        ON t2.number = t1.number
           AND t2.rn = t1.rn;