T-SQL "Joining"表到另一个表，并将前 3 条记录收集为列

Question

我想不出比这更好的表达方式了。

我有几张桌子：

Postcode | One | Two | Three
2000     |     |     |
2001     |     |     |
2002     |     |     |

等等，该表中有10000个邮政编码，One、Two和Three列都为空。

我还有一张这样的桌子：

Postcode | details
2001     | "foo"
2020     | "bar"

基本上，第二个表有一些精选的邮政编码，以及一些东西。假设那里有2000个邮政编码。

我想做的是，用第二个表中在数字上最接近的邮政编码（无论是大还是小）填充第一个表的三个额外列，我知道这并不理想。

到目前为止，我已经这样做了（顺便说一句，我的Postcode列是varchar）：

SELECT 
    A.Postcode
    , ( SELECT 
            MAX(X.Postcode)
        FROM (
            SELECT
                CAST(B.Postcode AS INT) AS 'Postcode'
                , RANK() OVER (ORDER BY CAST(B.Postcode AS INT) ASC) AS 'rank'
            FROM [TableB] B with(nolock)
            WHERE CAST(B.Postcode AS INT) >= CAST(A.Postcode AS INT)
        ) X WHERE X.[rank] = 1
    ) AS ONE
FROM [TableA] A with(nolock)

这让我得到了第一个更高的邮政编码。但我需要它更高或更低，我需要三个，而不仅仅是一个。

我被困在这里了。

Answer 1

您可以使用ROW_NUMBER和CROSS APPLY:的组合

SQL Fiddle

SELECT
    a.PostCode,
    b.One,
    b.Two,
    b.Three
FROM TableA a
CROSS APPLY(
    SELECT
        One = MAX(CASE WHEN RN = 1 THEN x.PostCode END),
        Two = MAX(CASE WHEN RN = 2 THEN x.PostCode END),
        Three = MAX(CASE WHEN RN = 3 THEN x.PostCode END)
    FROM(
        SELECT *, 
            RN = ROW_NUMBER() OVER(ORDER BY CAST(t.PostCode AS INT))
        FROM(
            SELECT TOP 3 
                PostCode,
                Diff = ABS(CAST(PostCode AS INT) - CAST(a.PostCode AS INT))
            FROM Tableb
            WHERE
                PostCode <> a.PostCode
            ORDER BY ABS(CAST(PostCode AS INT) - CAST(a.PostCode AS INT)), CAST(PostCode AS INT)
        )t
    )x
)b

结果

PostCode   One        Two        Three
---------- ---------- ---------- ----------
2000       2001       2002       2003
2001       2000       2002       2003
2002       2000       2001       2003

---

样本数据

表A

PostCode   One        Two        Three
---------- ---------- ---------- ----------
2000       NULL       NULL       NULL
2001       NULL       NULL       NULL
2002       NULL       NULL       NULL

表B

PostCode
----------
2000
2001
2002
2003
2004
2005
2006
2007
2008