MS Access中能否用VBA编写自定义Split函数在SQL查询中拆分字段生成多行

你的需求完全可以实现，不需要创建临时表、编写数据宏，仅需提前准备1张永久可用的辅助序列表+VBA标量函数，即可直接在查询中调用实现多字段拆分生成多行的效果。

步骤1：创建永久辅助序列表Num #

你只需创建1次该表，后续所有拆分场景都可以复用：

-- 创建Num表，仅存储连续整数
CREATE TABLE Num (n INT PRIMARY KEY);

你可以手动插入0到你预估的最大拆分元素数（比如单个字段最多拆100个值就插入0~99），也可以运行以下VBA代码批量插入：

Sub FillNumTable()
    Dim i As Integer
    CurrentDb.Execute "DELETE FROM Num"
    For i = 0 To 200 ' 可根据实际需要调整最大值
        CurrentDb.Execute "INSERT INTO Num(n) VALUES(" & i & ")"
    Next
End Sub

步骤2：编写公共拆分函数 #

在Access标准模块中插入以下公共函数，保存即可：

Public Function GetSplitElement(strInput As String, delimiter As String, index As Integer) As String
    On Error Resume Next
    Dim arr As Variant
    ' 先清理输入首尾空格，再拆分
    arr = Split(Trim(strInput), delimiter)
    ' 索引合法则返回对应元素并清理空格，否则返回空值
    If index >= 0 And index <= UBound(arr) Then
        GetSplitElement = Trim(arr(index))
    Else
        GetSplitElement = ""
    End If
End Function

步骤3：编写查询实现拆分 #

单字段拆分（对应你的第一个需求） #

直接运行以下查询即可得到预期结果：

SELECT 
    myTable.Field1,
    GetSplitElement(myTable.Field2, ",", Num.n) AS SplitField2
FROM myTable, Num
WHERE GetSplitElement(myTable.Field2, ",", Num.n) <> ""
ORDER BY myTable.Field1, Num.n;

多字段同时拆分（对应你的第二个需求） #

通过Num表别名即可实现多字段拆分的笛卡尔积效果，和你期望的结果完全一致：

SELECT 
    GetSplitElement(myTable.Field1, ";", n1.n) AS SplitField1,
    GetSplitElement(myTable.Field2, ",", n2.n) AS SplitField2
FROM myTable, Num AS n1, Num AS n2
WHERE 
    GetSplitElement(myTable.Field1, ";", n1.n) <> ""
    AND GetSplitElement(myTable.Field2, ",", n2.n) <> ""
ORDER BY SplitField1, SplitField2;

注意事项 #

• 请确保Num表的最大整数大于你所有待拆分字段的最大元素个数，若后续有更长的拆分需求，只需给Num表补充更大的整数即可
• 若不需要自动清理元素前后的空格，删除VBA函数中的Trim调用即可
• 普通业务场景下该方案性能远高于临时表+数据宏的实现，只要Num表数值范围合理，处理上万条记录无压力

内容的提问来源于stack exchange，提问作者Frank Lin