proc SQL 生成两个街道编号之间的每个街道编号并保留邮政编码

Question

我有一个数据集，其中包含带有街道名称和邮政编码的劣质和高级街道编号。我想生成一个数据集，其中包含每个街道编号之间的每个数字。

例如，我有这个数据集：

Inferior_Street_Number  Superior_street_number Zip_code
100                     105                    G1W2X5
100                     105                    G1W2X7
202                     204                    G1W2X7

并希望有这样的输出：

Street number    Zip_code
100              G1W2X5
101              G1W2X5
102              G1W2X5
103              G1W2X5
104              G1W2X5
105              G1W2X5
100              G1W2X7
101              G1W2X7
102              G1W2X7
103              G1W2X7
104              G1W2X7
105              G1W2X7
202              G1W2X7
203              G1W2X7
204              G1W2X7

我真的很感激你的帮助。

谢谢！尼克(对不起我的英语(

Answer 1

如果您有序列号表，则此问题分解为相当简单的连接操作。下面是一个 Transact-SQL 示例，其中大部分内容都是制作序列：

-- Sample sequence table, there are tons of ways to generate these
DECLARE @Sequence TABLE(
    Number INT PRIMARY KEY
)
INSERT
    @Sequence( Number )
VALUES
    (0), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )
INSERT
    @Sequence( Number )
SELECT
    Ones.Number + 10 * Tens.Number + 100 * Hundreds.Number + 1000 * Thousands.Number
FROM
    @Sequence AS Ones
CROSS JOIN
    @Sequence AS Tens
CROSS JOIN
    @Sequence AS Hundreds
CROSS JOIN
    @Sequence AS Thousands
EXCEPT
SELECT Number FROM @Sequence

SELECT
    [Sequence].Number AS StreetNumber
,   ZipCodes.Zip_code
FROM
    @ZipCodes AS ZipCodes
JOIN    @Sequence AS [Sequence] ON (
        [Sequence].Number BETWEEN ZipCodes.Inferior_Street_Number AND ZipCodes.Superior_Street_Number
    )   
ORDER BY
    Zip_code, StreetNumber