Sub HG20615()
Dim Cnn As ADODB.Connection
Set Cnn = CreateConnection("E:\MyDrawing\MyDrawing\mdb\FGB.mdb")
Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset
Dim Sql As String
Sql = "SELECT a.*,b.*,c.* FROM ((HG20617 as a " + _
"INNER JOIN HG20633IT as b on a.公称通径DN = b.公称通径DN) " + _
"OUTER JOIN HG20634B as c on a.公称通径DN = c.公称通径DN) "
'出错,将Outer更改为Left程序通过,但不是我要的排序。
Sql = "SELECT a.*,b.*,c.* FROM ((HG20617 as a " + _
"INNER JOIN HG20633IT as b on a.公称通径DN = b.公称通径DN) " + _
"LEFT JOIN HG20634B as c on a.公称通径DN = c.公称通径DN) "
Debug.Print Sql
Rst.Open Sql, Cnn
Dim xlSheet As Object
Set xlSheet = ConnectExcel("Sheet1")
xlSheet.range("a:z").ClearContents
xlSheet.range("A2").CopyFromRecordset Rst
With Rst.Fields
For jj = 0 To .Count - 1
xlSheet.Cells(1, jj + 1).Value = .Item(jj).Name
Next jj
End With
End SubFunction ConnectExcel(InputSheetName As String) As Object
Dim xlApp As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
Set ConnectExcel = xlApp.ActiveWorkbook.Sheets(InputSheetName)
End Function
Dim Cnn As ADODB.Connection
Set Cnn = CreateConnection("E:\MyDrawing\MyDrawing\mdb\FGB.mdb")
Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset
Dim Sql As String
Sql = "SELECT a.*,b.*,c.* FROM ((HG20617 as a " + _
"INNER JOIN HG20633IT as b on a.公称通径DN = b.公称通径DN) " + _
"OUTER JOIN HG20634B as c on a.公称通径DN = c.公称通径DN) "
'出错,将Outer更改为Left程序通过,但不是我要的排序。
Sql = "SELECT a.*,b.*,c.* FROM ((HG20617 as a " + _
"INNER JOIN HG20633IT as b on a.公称通径DN = b.公称通径DN) " + _
"LEFT JOIN HG20634B as c on a.公称通径DN = c.公称通径DN) "
Debug.Print Sql
Rst.Open Sql, Cnn
Dim xlSheet As Object
Set xlSheet = ConnectExcel("Sheet1")
xlSheet.range("a:z").ClearContents
xlSheet.range("A2").CopyFromRecordset Rst
With Rst.Fields
For jj = 0 To .Count - 1
xlSheet.Cells(1, jj + 1).Value = .Item(jj).Name
Next jj
End With
End SubFunction ConnectExcel(InputSheetName As String) As Object
Dim xlApp As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
Set ConnectExcel = xlApp.ActiveWorkbook.Sheets(InputSheetName)
End Function
其次,以下 SQL 等价
SELECT a.*, c.*
FROM a
FULL OUTER JOIN c on a.ID = c.ID
[code=SQL]SELECT a.*, NULL, NULL, NULL...
FROM a
WHERE NOT EXISTS (SELECT * FROM c WHERE a.ID = c.ID)
UNION
SELECT NULL, NULL, NULL..., c.*
FROM c
WHERE NOT EXISTS (SELECT * FROM a WHERE a.ID = c.ID)/code]