两表全外连接（full outer join)问题，求解？

Question

两表根据col 列做全外连接（full outer join)， 若多行连接上只取id列值最近的一条(两表id列差值最小的）相连表A                表B
id  col1         id    col2
--------         --------------
1    A           3     B
2    B           5     B
6    C           7     C
11   F           9     D
15   F           14    F
18   H           16    G结果集
id  col1         id     col2
-------------------------------
1    A
2    B           3     B
                 5     B
6    C           7     C
                 9     D
11   F
15   F           14    F
                 16    G
18   H
-------------------------------先前的问题没解决，现把问题简化出最关键部分，继续求助？
http://topic.csdn.net/u/20081224/17/4eb470eb-818e-4a9e-992e-15ab87e5ae27.html

Answer 1

生成行的顺序最好能和我给出的一样，按业务规则（A->Z)，因为做全外连接后顺序肯定会乱。

Answer 2

---测试数据---
if object_id('[表A]') is not null drop table [表A]
go
create table [表A]([id] int,[col1] varchar(1))
insert [表A]
select 1,'A' union all
select 2,'B' union all
select 6,'C' union all
select 11,'F' union all
select 15,'F' union all
select 18,'H'
if object_id('[表B]') is not null drop table [表B]
go
create table [表B]([id] int,[col2] varchar(1))
insert [表B]
select 3,'B' union all
select 5,'B' union all
select 7,'C' union all
select 9,'D' union all
select 14,'F' union all
select 16,'G'---查询---
select isnull(cast(a.id as varchar),'') [a.id],isnull(a.col1,'') [a.col1],isnull(cast(b.id as varchar),'') [b.id],isnull(b.col2,'') [b.col2]  from(
select * from 表A
union all
select * from 表B) t
left join 表A a on a.id=t.id
left join 表B b on b.id=t.id
order by t.id---结果---
a.id                           a.col1 b.id                           b.col2 
------------------------------ ------ ------------------------------ ------ 
1                              A                                     
2                              B                                     
                                      3                              B
                                      5                              B
6                              C                                     
                                      7                              C
                                      9                              D
11                             F                                     
                                      14                             F
15                             F                                     
                                      16                             G
18                             H                                     （所影响的行数为 12 行）

Answer 3

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-11-28 19:16:11
---------------------------------
 
--> 生成测试数据: @表A
DECLARE @表A TABLE (id INT,col1 VARCHAR(1))
INSERT INTO @表A
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 11,'F' UNION ALL
SELECT 15,'F' UNION ALL
SELECT 18,'H'
 
--> 生成测试数据: @表B
DECLARE @表B TABLE (id INT,col2 VARCHAR(1))
INSERT INTO @表B
SELECT 3,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 7,'C' UNION ALL
SELECT 9,'D' UNION ALL
SELECT 14,'F' UNION ALL
SELECT 16,'G'--SQL查询如下:SELECT
    ISNULL(RTRIM(A.id),'') AS aid,
    ISNULL(A.col1,'') AS col1,
    ISNULL(RTRIM(B.id),'') AS bid,
    ISNULL(B.col2,'') AS col2
FROM(
    SELECT
        id,col1,
        rid=ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY id)
    FROM @表A
) AS A
    FULL JOIN (
        SELECT
            id,col2,
            rid=ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY id)
        FROM @表B
    ) AS B
        ON A.col1=B.col2
            AND A.rid=B.rid/*
aid          col1 bid          col2
------------ ---- ------------ ----
1            A                 
2            B    3            B
                  5            B
6            C    7            C
                  9            D
11           F    14           F
15           F                 
                  16           G
18           H                 (9 行受影响)*/

Answer 4

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-11-28 19:16:11
---------------------------------
 
--> 生成测试数据: @表A
DECLARE @表A TABLE (id INT,col1 VARCHAR(1))
INSERT INTO @表A
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 11,'F' UNION ALL
SELECT 15,'F' UNION ALL
SELECT 18,'H'
 
--> 生成测试数据: @表B
DECLARE @表B TABLE (id INT,col2 VARCHAR(1))
INSERT INTO @表B
SELECT 3,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 7,'C' UNION ALL
SELECT 9,'D' UNION ALL
SELECT 14,'F' UNION ALL
SELECT 16,'G'--SQL查询如下:--2000SELECT
    ISNULL(RTRIM(A.id),'') AS aid,
    ISNULL(A.col1,'') AS col1,
    ISNULL(RTRIM(B.id),'') AS bid,
    ISNULL(B.col2,'') AS col2
FROM(
    SELECT
        id,col1,
        rid=(SELECT COUNT(*)
             FROM @表A 
             WHERE id<=T.id
                 AND col1=T.col1)
    FROM @表A AS T
) AS A
    FULL JOIN (
        SELECT
            id,col2,
            rid=(SELECT COUNT(*) 
                 FROM @表B
                 WHERE id<=T.id
                     AND col2=T.col2)
        FROM @表B AS T
    ) AS B
        ON A.col1=B.col2
            AND A.rid=B.rid
ORDER BY ISNULL(A.id,B.id)/*
aid          col1 bid          col2
------------ ---- ------------ ----
1            A                 
2            B    3            B
                  5            B
6            C    7            C
                  9            D
11           F    14           F
15           F                 
                  16           G
18           H                 (9 行受影响)
*/

Answer 5

11           F    14           F
15           F                 
-----------------------------------
这个应为
11           F   
15           F    14           F
            

Answer 6

接小梁MM的班DECLARE @表A TABLE (id INT,col1 VARCHAR(1))
INSERT INTO @表A
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 11,'F' UNION ALL
SELECT 15,'F' UNION ALL
SELECT 18,'H'
 
--> 生成测试数据: @表B
DECLARE @表B TABLE (id INT,col2 VARCHAR(1))
INSERT INTO @表B
SELECT 3,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 7,'C' UNION ALL
SELECT 9,'D' UNION ALL
SELECT 14,'F' UNION ALL
SELECT 16,'G'declare @表C TABLE (aid INT,col1 VARCHAR(1),bid INT,col2 VARCHAR(1),diff INT)
declare @表D TABLE (aid INT,col1 VARCHAR(1),diff INT)
declare @表E TABLE (bid INT,col2 VARCHAR(1),diff INT)
--SQL查询如下:insert into @表C
select A.id AS aid, A.col1 AS col1, B.id AS bid, B.col2 AS col2, abs(A.id-B.id) as diff
From @表A A full join @表B B ON A.col1=B.col2;insert into @表D
select aid,col1,diff from @表C c1 where c1.aid is not null and not exists 
(select 1 from @表C c2 where c1.aid=c2.aid and c1.col1=c2.col1 and c1.diff>c2.diff );insert into @表E
select bid,col2,diff from @表C c1 where c1.bid is not null and not exists 
(select 1 from @表C c2 where c1.bid=c2.bid and c1.col2=c2.col2 and c1.diff>c2.diff );select 
    ISNULL(RTRIM(d.aid),'') AS aid,
    ISNULL(d.col1,'') AS col1,
    ISNULL(RTRIM(e.bid),'') AS bid,
    ISNULL(e.col2,'') AS col2
From @表D d full join @表E e on d.col1=e.col2 and d.diff=e.diff
ORDER BY ISNULL(d.aid,e.bid)/*结果
aid          col1 bid          col2
------------ ---- ------------ ----
1            A                 
2            B    3            B
                  5            B
6            C    7            C
                  9            D
11           F                 
15           F    14           F
                  16           G
18           H                 (9 行受影响)
*/

Answer 7

DECLARE @表A TABLE (id int,col1 VARCHAR(1))
INSERT INTO @表A
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 11,'F' UNION ALL
SELECT 15,'F' UNION ALL
SELECT 18,'H'DECLARE @表B TABLE (id int,col2 VARCHAR(1))
INSERT INTO @表B
SELECT 3,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 7,'C' UNION ALL
SELECT 9,'D' UNION ALL
SELECT 14,'F' UNION ALL
SELECT 16,'G'
select id=isnull(cast(a.id as varchar(5)),''),col1=isnull(col1,''),
id=isnull(cast(b.id as varchar(5)),''),col2=isnull(col2,'')
from @表A a
full join @表B b on a.col1=b.col2 
and not exists(select 1 from @表A where col1=b.col2 and abs(id-b.id)<abs(a.id-b.id))
and not exists(select 1 from @表B where col2=a.col1 and abs(id-a.id)<abs(a.id-b.id))
order by isnull(col1,col2)/*
id    col1 id    col2
----- ---- ----- ----
1     A          
2     B    3     B
           5     B
6     C    7     C
           9     D
11    F          
15    F    14    F
           16    G
18    H    
*/