Sql 如何使用Split函数。

ning84

Private Sub dfewa()
    Dim Rng As Range, Str
        Set Rng = Selection
        Str = Rng
        Str = "E:\Temp\StreetSnap\2024年\2024年05月\2024年05月19日\IMG_20240519_151306275.jpg"
        ''Debug.Print Mid(Str, InStrRev(Str, "\") + 1, Len(Str))
        ss = Split(Str, "\")
        For ii = 0 To UBound(ss)
            Debug.Print ss(ii)
        Next ii
        Stop
   End Sub


结果

E:
Temp
StreetSnap
2024年
2024年05月
2024年05月19日
IMG_20240519_151306275.jpg
*********************************
目标需求，Split函数，如何在下面代码中得到应用。
Str = "select S From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where S like '%jpg'"

********************************

1. 
   
2. Function SqlRetuRs(Str)
   
3.    Dim Cn As ADODB.Connection
   
4.        Set Cn = New ADODB.Connection
   
5.    Dim Rs As ADODB.Recordset
   
6.        Set Rs = New ADODB.Recordset
   
7.         'Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
   
8.         Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
   
9.         Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
   
10.       
    
11.        Set SqlRetuRs = Rs
    
12. End Function
    
13. 
    
14. 
    
15. 
    
16. 
    
17. 
    
18. Sub DELDELDEL()
    
19.    Dim Rng As Range
    
20.        Set Rng = Selection
    
21.    Dim Sht As Worksheet
    
22.        Set Sht = Rng.Parent
    
23.        Set Rng = Sht.Range(Sht.Cells(3, "Q").Formula)
    
24.       
    
25.    Dim Str, Rs As ADODB.Recordset
    
26.      
    
27.       Str = "select S From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where S like '%jpg'"
    
28.      
    
29.       Debug.Print Str
    
30.       Set Rs = SqlRetuRs(Str)
    
31.       
    
32.       Debug.Print Rs.RecordCount, Rs.Fields.Count
    
33.       Rs.MoveFirst
    
34.       For ii = 0 To Rs.RecordCount - 1
    
35.            Debug.Print Rs.Fields(0)
    
36.            Rs.MoveNext
    
37.       Next ii
    
38. End Sub
    

复制代码

ning84

钻牛角尖，把人整死，老旧机器循环按几个小时算。用SQL效率高多了。

SQL直接实现 \StreetSnap\2024年\2024年03月\2024年03月01日\IMG_20240301_172557636.jpg

1. 
   
2. 
   
3. 
   
4. Function SqlRetuRs(Str)
   
5. 
   
6.    Dim Cn As ADODB.Connection
   
7.        Set Cn = New ADODB.Connection
   
8.    Dim Rs As ADODB.Recordset
   
9.        Set Rs = New ADODB.Recordset
   
10.         '
    
11.        If InStr(UCase(Application.Path), "WPS") > 0 Then
    
12.            Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
    
13.        Else
    
14.           Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
    
15.        End If
    
16.         Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
    
17.       
    
18.        Set SqlRetuRs = Rs
    
19. End Function
    
20. 
    
21. 
    
22. 
    
23. 
    
24. 
    
25. Sub DELDELDEL()
    
26.    Dim RootName
    
27.        RootName = "StreetSnap"
    
28.    Dim Rng As Range, oRng As Range
    
29.        Set Rng = Selection
    
30.    Dim Sht As Worksheet
    
31.        Set Sht = Rng.Parent
    
32.        Set Rng = Sht.Range(Sht.Cells(4, 1).Formula)
    
33.        Debug.Print Address
    
34.    Dim Str, Rs As ADODB.Recordset
    
35.    Dim Rr
    
36.       Rr = 8
    
37.       'Str = "select Distinct   '\' + format(oDate,'yyyy年'),"
    
38.       
    
39.       'Str = Str & "'\' + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月'),"
    
40.       Str = "select Distinct "
    
41.       Str = Str & "'" & RootName & "\'"
    
42.       Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' , "
    
43.       Str = Str & "Name From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
44.       '
    
45.       Str = "select Distinct format(oDate,'yyyy年mm月dd日 hh:mm:ss'),Name,"
    
46.       Str = Str & "'" & RootName & "\'"
    
47.       Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' + Name "
    
48.       Str = Str & " From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
49.      
    
50.       Debug.Print Str
    
51.       
    
52.       
    
53.       Set Rs = SqlRetuRs(Str)
    
54.       Set oRng = Sht.Cells(Rr, 1)
    
55.       oRng.CopyFromRecordset Rs
    
56.    
    
57. End Sub
    
58. 
    

复制代码

ning84

复杂的事，SQL简单化了。





这个代码，还有很大的优化空间。

1. 
   
2. 
   
3. 
   
4. Function SqlRetuRs(Str)
   
5. 
   
6.    Dim Cn As ADODB.Connection
   
7.        Set Cn = New ADODB.Connection
   
8.    Dim Rs As ADODB.Recordset
   
9.        Set Rs = New ADODB.Recordset
   
10.         '
    
11.        If InStr(UCase(Application.Path), "WPS") > 0 Then
    
12.            Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
    
13.        Else
    
14.           Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
    
15.        End If
    
16. 
    
17.        Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
    
18.        Set SqlRetuRs = Rs
    
19. End Function
    
20. 
    
21. 
    
22. 
    
23. 
    
24. 
    
25. Sub DELDELDEL()
    
26.    Dim RootName
    
27.        RootName = "StreetSnap"
    
28.    Dim Rng As Range, oRng As Range
    
29.        Set Rng = Selection
    
30.    Dim oDate As Date
    
31.        'oDate = Selection
    
32.       
    
33.    Dim Sht As Worksheet
    
34.        Set Sht = Rng.Parent
    
35.        Set Rng = Sht.Range(Sht.Cells(4, 1).Formula)
    
36.        Debug.Print Address
    
37.    Dim Str, Rs As ADODB.Recordset
    
38.    Dim Rr
    
39.       Rr = 8
    
40.       'Str = "select Distinct   '\' + format(oDate,'yyyy年'),"
    
41.       
    
42.       'Str = Str & "'\' + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月'),"
    
43.       Str = "select Distinct "
    
44.       Str = Str & "'" & RootName & "\'"
    
45.       Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' , "
    
46.       Str = Str & "Name From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
47.       '
    
48.       Str = "select Distinct format(oDate,'yyyy年mm月dd日 hh:mm:ss'),Name,"
    
49.       Str = Str & "'" & RootName & "\'"
    
50.       Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' + Name "
    
51.       Str = Str & " From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
52.      
    
53.       Debug.Print Str
    
54.       
    
55.       Str = "select Distinct "
    
56.       Str = Str & "'" & RootName & "\' + format(oDate,'yyyy年')"
    
57.       'Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' + Name "
    
58.       Str = Str & " From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
59.      
    
60.       Debug.Print Str
    
61.       
    
62.       
    
63.       
    
64.       Set Rs = SqlRetuRs(Str)
    
65.       Set oRng = Sht.Cells(Rr, 10)
    
66.       oRng.CopyFromRecordset Rs
    
67.       
    
68.       Str = "select Distinct "
    
69.       Str = Str & "'" & RootName & "\' + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' "
    
70.       'Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\' + Name "
    
71.       Str = Str & " From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
72.      
    
73.       Debug.Print Str
    
74.       
    
75.       
    
76.       
    
77.       Set Rs = SqlRetuRs(Str)
    
78.       Set oRng = Sht.Cells(Rr, 11)
    
79.       oRng.CopyFromRecordset Rs
    
80.       
    
81.       Str = "select Distinct "
    
82.       'Str = Str & "'" & RootName & "\' + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' "
    
83.       Str = Str & " + format(oDate,'yyyy年') + '\' + format(oDate,'yyyy年mm月') + '\' + format(oDate,'yyyy年mm月dd日') + '\'  "
    
84.       Str = Str & " From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Where Name like '%jpg'"
    
85.      
    
86.       Debug.Print Str
    
87.       
    
88.       
    
89.       
    
90.       Set Rs = SqlRetuRs(Str)
    
91.       Set oRng = Sht.Cells(Rr, 12)
    
92.       oRng.CopyFromRecordset Rs
    
93.       
    
94.    
    
95. End Sub
    
96. 
    

复制代码