我有两个表(table_A,Table_B)和
需要有关子查询的帮助或从列中选择多个值的可能解决方案 TABLE_A.DESCRIPString
TABLE_A:
ID PARTNUM DESCRIPString
1 4456 121~134~111
2 4457 122~111
3 4458 122~134
4 4459 111
5 4460 121~134~111
交叉搭配 TABLE_B.DESCRIPID
TABLE_B:
ID DESCRIPID DECSRIPLong
1 121 Silver
2 122 Black
3 111 Mask
4 134 Pickle
最后显示以下内容:
Table_AB
ID PARTNUM DESCRIPString PARTDESCRIP
1 4456 121~134~111 Silver~Pickle~Mask
2 4457 122~111 Black~Mask
3 4458 122~134 Black~Pickle
4 4459 111 Mask
5 4460 121~134~111 Silver~Pickle~Mask
我知道大多数人会建议将数据库重做为每列单个变量,但是在这种情况下不能这样做。
将此函数放到模块中并运行此查询。它应该给您所要求的结果。您没有指定数据类型,因此我在函数中使用了变体。
Public Function fnParseDescr(strWork As Variant) As String
Dim vEnd
Dim strTemp As String
Dim strLook As String
Dim strResult As String
strResult = ""
strWork = Trim(CStr(strWork))
Do While Len(strWork) > 0
If InStr(strWork, "~") = 0 Then
strLook = Trim(DLookup("[DESCRIPLong]", "TABLE_B", "[DESCRIPID] = CVar('" & strWork & "')"))
strResult = strResult & "~" & strLook
strWork = ""
Else
vEnd = InStr(strWork, "~") - 1
strTemp = Left(strWork, vEnd)
strLook = Trim(DLookup("[DESCRIPLong]", "TABLE_B", "[DESCRIPID] = CVar('" & strTemp & "')"))
strResult = strResult & "~" & strLook
strWork = Right(strWork, (Len(strWork) - (vEnd + 1)))
strTemp = ""
strLook = ""
End If
Loop
If Left(strResult, 1) = "~" Then strResult = Right(strResult, (Len(strResult) - 1))
fnParseDescr = strResult
Exit Function
End Function
SELECT ID,
PARTNUM,
DESCRIPString,
fnParseDescr([DESCRIPString]) AS PARTDESCRIP
FROM TABLE_A;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句