扩展SQL中的数字范围

Question

是否有可以编写的SQL代码来扩展目标Zip范围列中的这些数字集群？截图附后

使用Access 2016，谢谢

​

​

Answer 1

您将需要DAO来运行此操作。

创建一个帮助函数来拆分邮政编码：

Public Function ExpandCluster(ByVal ClusterList As String) As Variant

    Dim Clusters    As Variant
    Dim Items       As Variant
    Dim ZipCodes()  As String

    Dim Index       As Integer
    Dim FirstCode   As Integer
    Dim LastCode    As Integer
    Dim ThisCode    As Integer
    Dim Redimmed    As Boolean

    Clusters = Split(ClusterList, ",")
    ReDim ZipCodes(0)
    For Index = LBound(Clusters) To UBound(Clusters)
        Items = Split(Clusters(Index), "-")
        FirstCode = Val(Items(LBound(Items)))
        LastCode = Val(Items(UBound(Items)))
        While FirstCode <= LastCode
            Debug.Print Index, FirstCode
            If Redimmed Then
                ReDim Preserve ZipCodes(UBound(ZipCodes) + 1)
            Else
                Redimmed = True
            End If
            ZipCodes(UBound(ZipCodes)) = Format(FirstCode, "000")
            FirstCode = FirstCode + 1
        Wend
    Next

    ExpandCluster = ZipCodes

End Function

然后在一个函数中使用此方法，为使用该函数提取的每个邮政编码创建一个记录：

Public Sub FillTable()

    Dim Source      As DAO.Recordset
    Dim Target      As DAO.Recordset

    Dim ZipCodes()  As String
    Dim Index       As Integer

    Set Source = CurrentDb.OpenRecordset("Select * From tblParent Where ZipRange Is Not Null")
    Set Target = CurrentDb.OpenRecordset("Select * From tblChild")

    While Not Source.EOF
        ZipCodes = ExpandCluster(Source!ZipRange.Value)
        For Index = LBound(ZipCodes) To UBound(ZipCodes)
            Target.AddNew
                ' Assign foreign key.
                Target!FK.Value = Source!Id.Value
                ' Assign this zip code.
                Target!ZipCode.Value = ZipCodes(Index)
                '
                ' Insert lines for other field values. 
                '
            Target.Update
        Next
        Source.MoveNext
    Wend
    Source.Close
    Target.Close

End Sub

使用了一些通用名称。当然，根据实际的表名和字段名来调整它们。