代码之家 › 专栏 › 技术社区 › shsteimer

SQLServer:如何对任意(长度不等)序列的有序数据进行配对?

sql-server-2000 tsql sql-server sql

shsteimer · 技术社区 · 17 年前

下面是我的场景:我有两个表A,B(为了这个问题,它们是相同的):

ID 
1
2

表A:

ID FKID Value Sort
1  1    a     1
2  1    aa    2
3  1    aaa   3
4  2    aaaa  1
5  2    aaaaa 2

表B:

ID FKID Value Sort
1  1    b     1
2  1    bb    2
3  2    bbb   1
4  2    bbbb  2
5  2    bbbbb 3

期望输出:

FKID ValueA ValueB Sort
1    a      b      1
1    aa     bb     2
1    aaa    (null) 3
2    aaaa   bbb    1
2    aaaaa  bbbb   2
2    (null) bbbbb  3

1. 有3-As和2-Bs和记录 2. 具有2-As和3-Bs,它们都由Sort integer列很好地配对。

我目前的解决方案是使用数字表进行交叉连接。它可以工作,但由于这些表中的项数是无界的,所以我的数字表比较大(应用程序在理论上是无界的,但实际上,我可以将其限制为1000)。

还有一件事: 我被SQL Server 2000卡住了 :P。

更新:

更新: 完整解决方案:

DECLARE @X AS TABLE (ID INT)
DECLARE @A AS TABLE (ID INT, FKID INT, Value VARCHAR(10), Sort INT)
DECLARE @B AS TABLE (ID INT, FKID INT, Value VARCHAR(10), Sort INT)

INSERT INTO @X (ID) VALUES (1)
INSERT INTO @X (ID) VALUES (2)

INSERT INTO @A (ID, FKID, Value, Sort) VALUES (1, 1, 'a',     1)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (2, 1, 'aa',    2)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (3, 1, 'aaa',   3)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (4, 2, 'aaaa',  1)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (5, 2, 'aaaaa', 2)

INSERT INTO @B (ID, FKID, Value, Sort) VALUES (1, 1, 'b',     1)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (2, 1, 'bb',    2)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (3, 2, 'bbb',   1)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (4, 2, 'bbbb',  2)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (5, 2, 'bbbbb', 3)

SELECT * FROM @X
SELECT * FROM @A
SELECT * FROM @B

SELECT COALESCE(A.FKID, B.FKID) ID
  ,A.Value
  ,B.Value
  ,COALESCE(A.Sort, B.Sort) Sort
FROM @X X
LEFT JOIN @A A ON A.FKID = X.ID
FULL OUTER JOIN @B B ON B.FKID = A.FKID AND B.Sort = A.Sort

4 回复 | 直到 17 年前

Brian Schantz 17 年前

select 
    coalesce(a.fkid, b.fkid) fkid, 
    A.Value as ValueA, 
    B.Value as ValueB, 
    coalesce(a.sort, b.sort) Sort
from a full outer join b
        on a.fkid = b.fkid
        and a.sort = b.sort
order by fkid, sort

Charles Bretana 17 年前

我不是100%清楚你在追求什么,但是试试这个,看看它是否是你想要的

Select Coalesce(a.FKID, b.FKID) FKID,
    a.Value, B.Value, 
    Coalesce(a.Sort, b.Sort) Sort
From TableA a Full Join TableB b
    On a.Sort = b.sort
       And Left(a.value,1) = 'a'
       And Left(b.value,1) = 'b'
       And Len(a.value) = Len(b.value)

shahkalpesh 17 年前

SELECT A.FKID,  A.Value AS ValueA, B.Value AS ValueB, A.Sort
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID
UNION 
SELECT B.FKID,  A.Value AS ValueA, B.Value AS ValueB, B.Sort
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID

如果从查询中删除排序字段,您将得到您正在查找的结果。

SELECT A.FKID,  A.Value AS ValueA, B.Value AS ValueB, A.Sort AS ASort, 
B.Sort AS BSort
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID
UNION 
SELECT B.FKID,  A.Value AS ValueA, B.Value AS ValueB,
A.Sort AS ASort, B.Sort AS BSort
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID

manji 17 年前

select COALESCE(tt1.FKID, tt2.FKID) FKID, 
       tt1.Value ValueA, 
       tt2.Value ValueB,
       CASE WHEN tt1.Sort IS NULL OR tt2.Sort IS NULL
            THEN COALESCE(tt1.Sort, tt2.Sort)
            ELSE CASE WHEN tt1.Sort >= tt2.Sort 
                      THEN tt1.Sort 
                      ELSE tt2.Sort
                 END
       END Sort 
from tt1
full join tt2 on tt1.FKID = tt2.FKID and len(tt1.value) = len(tt2.value)
order by COALESCE(tt1.FKID, tt2.FKID)