在CSDN论坛上看到一个数据库帖子，觉得挺有意思的，特转过来跟大家分享

declare @test1 table(A int)
declare @test2 table(B int)

insert @test1
select 1 union all
select 3 union all
select 5 union all
select 7 union all
select 9 union all
select 11 

insert @test2
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 8 union all
select 10 union all
select 11 union all
select 12 

希望输出的结果是：

1 1

0 2

3 0

0 4

5 0

0 6

7 0

0 8

9 0

0 10

11 11

0 12

就是A=B的话在同一行,然后要把他们考虑在一起排序

大家先看看我自己写的语句

DECLARE @Test3 TABLE (A INT, B INT)
INSERT INTO @Test3 (A)
SELECT A FROM @Test1 ORDER BY A ASC

INSERT INTO @Test3 (A)
SELECT B FROM @Test2 ORDER BY B ASC

UPDATE t3 SET t3.B = t3.A
FROM @Test3 t3  
WHERE t3.A IN (SELECT B FROM @Test2)

DECLARE @Test4 TABLE (A INT, B INT)

INSERT INTO @Test4 (A,B)
SELECT A,B  
FROM @Test3 GROUP BY A,B

UPDATE t4 SET t4.A = NULL  
FROM @Test4 t4
WHERE t4.A NOT IN (SELECT A FROM @Test1)

UPDATE @Test4 SET A = 0 WHERE A IS NULL
UPDATE @Test4 SET B = 0 WHERE B IS NULL

DELETE @Test3 WHERE A IS NULL AND B IS NULL

SELECT * FROM @Test4

效果虽然能实现，但是超级复杂，运行效率极差，但我看到了一段很棒的代码：

select isnull(a.A,0) as A,isnull(b.B,0) as B 
from @test1 a full join @test2 b on a.A=b.B
order by coalesce(a.A,b.B,0)

里面用到了 full join 、coalesce 两个技术。

真是没想到，sql提供了full join功能，很是佩服

对于coalesce函数，是返回货号中，第一个不为null值的数据，正好进行排序操作。佩服佩服佩服哇～

第一次见到full join、coalesce这两个关键字的使用，高，高，高。。。