如何使用Union All，将表1的2个fields与表2的3Fields合成一个表？

ning84

Select oName,SubFolderCount,,oFolderPath  From [Sheet3$T10:Y3250] Union All Select oName,SubFolderCount From [Sheet3$A10:H603]
测试这条语句，结果出错。

1. Sub dedel()
   
2.    Dim Rng As Range
   
3.    Dim R1 As Range, R2 As Range
   
4.        Set R1 = Selection
   
5.    Dim Sht As Worksheet
   
6.        Set Sht = Sheet3
   
7.        With Sht
   
8.            Set R1 = .Range(.Cells(4, 1).Formula)
   
9.            Set R2 = .Range(.Cells(4, 2).Formula)
   
10.        End With
    
11.    Dim SqlShtStr1, SqlShtStr2
    
12.        SqlShtStr1 = " From [" & Sht.Name & "$" & R1.Address(0, 0) & "] "
    
13.        SqlShtStr2 = " From [" & Sht.Name & "$" & R2.Address(0, 0) & "] "
    
14.        Debug.Print R1.Address, R2.Address
    
15.    Dim SqlStr
    
16.        SqlStr = "Select oName,SubFolderCount,,oFolderPath " & SqlShtStr1 & "Union All Select oName,SubFolderCount,' '" & SqlShtStr2
    
17.        Debug.Print SqlStr
    
18.        Stop
    
19.    Dim Rs As ADODB.Recordset
    
20.        Set Rs = SqlRetuRs(SqlStr)
    
21.        Debug.Print Rs.RecordCount
    
22.        Set Rng = Sheet1.Cells(5, 1)
    
23.        Rng.CopyFromRecordset Rs
    
24. End Sub

复制代码

wang-way

用空格占位行不行，你试试

wang-way

select a,b,0 from A union select a,b,c from B

ning84

wang-way 发表于 2024-6-26 16:13
select a,b,0 from A union select a,b,c from B

谢谢回复。学会了0的用法。会需要精一不学习

1. Sub dedel()
   
2.    Dim Rng As Range
   
3.    Dim R1 As Range, R2 As Range
   
4.        Set R1 = Selection
   
5.    Dim Sht As Worksheet
   
6.        With Sheet1
   
7.             .Cells.Clear
   
8.             .Cells.Font.Size = 9
   
9.        End With
   
10.        Set Sht = Sheet3
    
11.        With Sht
    
12.            Set R1 = .Range(.Cells(4, 1).Formula)
    
13.            Set R2 = .Range(.Cells(4, 2).Formula)
    
14.        End With
    
15.    Dim SqlShtStr1, SqlShtStr2
    
16.        SqlShtStr1 = " From [" & Sht.Name & "$" & R1.Address(0, 0) & "] "
    
17.        SqlShtStr2 = " From [" & Sht.Name & "$" & R2.Address(0, 0) & "] "
    
18.        Debug.Print R1.Address, R2.Address
    
19.    Dim SqlStr
    
20.        SqlStr = "Select oName,Cc,0 " & SqlShtStr2 & "Where Cc>1 Union all Select oName,0,oFolderPath" & SqlShtStr1 & " Where Not oFolderPath like '%$%' and Not oFolderPath like '%Boot%'"
    
21.        SqlStr = SqlStr & " and Not oFolderPath like '%MSOCache%' and  Not oFolderPath like '%Backup%'"
    
22.        Debug.Print SqlStr
    
23.        ''
    
24.    Dim Rs As ADODB.Recordset
    
25.        Set Rs = SqlRetuRs(SqlStr)
    
26.        Debug.Print Rs.RecordCount
    
27.        Set Rng = Sheet1.Cells(5, 1)
    
28.        Rng.CopyFromRecordset Rs
    
29. End Sub
    

复制代码