如何在Excel中使用SQL连接 [英] How to use SQL Joins in Excel

问题描述

我看到一年前更新了一个问题，这使我在解决方案方面取得了长足的进展，但是导致了更多的问题。

让我开始说我不是SQL（DB2，SQLServer，Oracle）的新手，但我是在Excel中使用SQL的相对新手。另外，我在2010年工作，但必须保持与2003年的兼容性。

我想要做的是将一张表中的数据与另一张表进行匹配并报告异常。我想从开放线中选取6列，并将它们从后退订单中的3列进行匹配，仅显示例外。 'Open Lines'可以是1..n，'Back Orders'可以是0..n。我只想抓住后退订单存在的行。

要写正常的SQL，我会这样做：

  SELECT 
 O. [部件号]，O. [部分描述] O. [来源域]，O. [船舶数量]，O. [创建日期]，
 B. [目的域]，B. [数量]，B. [创建日期] 
 FROM [OPEN LINES] O 
加入[返回订单] B ON O 。[部件号] = B. [部件号] 
 WHERE B. [部件号] IS NOT NULL 
 ORDER BY O. [部件号] 
  / pre> 
 
 
问题是我无法通过连接而没有错误。这是我到目前为止。
  rs.OpenSELECT [OPEN LINES $]。[Part Number]，[OPEN LINES $]。[Part Desc]，$ _ 
[OPEN LINES $]。[Source Domain]，& _ 
[OPEN LINES $]。[发货数量]，[OPEN LINES $]。[创建日期]& _ 
FROM [OPEN LINES $]& _ 
JOIN [Back Orders $] ON [OPEN LINES $]。[Part Number] = [Back Orders $]。[Part Number]& _ 
ORDER BY [OPEN LINES $]。[Part Number];，cn 
  
我会感谢你可以提供的任何帮助。
 
 
 
 Gord 
解决方案
 p>尝试这样：
  rs.OpenSELECT [OPEN LINES $]。[部件号]，[OPEN LINES $] 。[Part]$ _ 
[OPEN LINES $]。[Source Domain]，& _ 
[OPEN LINES $]。[发货数量]，[OPEN LINES $]。[创建日期]& _ 
FROM [OPEN LINES $]，[Back Orders $]& _ 
WHERE [OPEN LINES $]。[Part Number] = [Back Orders $]。[Part Number]& _ 
ORDER BY [OPEN LINES $]。[Part Number]，cn 
  
你不需要结尾的分号。
 
 
 
这是一个更简单的例子（在XL2010中测试）
  Sub SqlJoin（）
 
 Dim oConn As New ADODB.Connection 
 Dim oRS As New ADODB.Recordset 
 Dim sPath 
 Dim sSQL As String 
 
 sSQL =从< t1> a，< t2> b中选择a.blah其中a.blah = b.blah
 
 sSQL = Replace（sSQL ，< t1>，Rangename（Sheet1.Range（A1：A5）））
 sSQL =替换（sSQL，< t2> ）））
 
如果ActiveWorkbook.Path<> 然后
 sPath = ActiveWorkbook.FullName 
 Else 
 MsgBox正在查询的工作簿必须先保存...
退出子
结束如果
 
 oConn.OpenProvider = Microsoft.ACE.OLEDB.12.0; Data Source ='& sPath& ; &安培; _ 
扩展属性='Excel 12.0; HDR =是; IMEX = 1'; 
 
 oRS.Open sSQL，oConn 
 
如果不是oRS.EOF然后
 Sheet1.Range（E1）。CopyFromRecordset oRS 
 Else 
 MsgBox找不到记录
如果
 
 oRS.Close 
 oConn.Close 
 
 End Sub 
 
函数Rangename（r As Range）As String 
 Rangename =[& r.Parent.Name& $& _ 
 r.Address（False，False）& ]
结束功能
  
 
I saw a question updated about a year ago that took me a long way towards the solution that I need but led to more questions than answers.

Let me start by saying that I'm not a "newbie" to SQL (DB2, SQLServer, Oracle) but I'm a relative "newbie" to using SQL in Excel. In addition, I'm working in 2010 but must retain compatibilty with 2003.

What I'm trying to do is match the data in one sheet to another and report exceptions. I want to pick up 6 columns from 'Open Lines' and match them to 3 columns from 'Back Orders' and only show the exceptions. 'Open Lines' can be 1..n and 'Back Orders' can be 0..n. I only want to grab the lines where 'Back Orders' exist.

To write normal SQL, I would do something like this:
SELECT 
O.[Part Number], O.[Part Desc], O.[Source Domain], O.[Ship Qty], O.[Date Created],
B.[Dest Domain], B.[Quantity], B.[Date Created] 
FROM [OPEN LINES] O
JOIN [Back Orders] B ON O.[Part Number] = B.[Part Number]
WHERE B.[Part Number] IS NOT NULL
ORDER BY O.[Part Number] 
The problem is that I can't get past the join without an error. This is what I have so far.
rs.Open "SELECT [OPEN LINES$].[Part Number], [OPEN LINES$].[Part Desc], " $ _
"[OPEN LINES$]. [Source Domain], " & _
"[OPEN LINES$].[Ship Qty], [OPEN LINES$].[Date Created] " & _
"FROM [OPEN LINES$] " & _
"JOIN [Back Orders$] ON [OPEN LINES$].[Part Number] = [Back Orders$].[Part Number] " & _
"ORDER BY [OPEN LINES$].[Part Number] ;", cn
I would appreciate any help that you can provide.

Gord
解决方案
Try this:
rs.Open "SELECT [OPEN LINES$].[Part Number], [OPEN LINES$].[Part Desc], " $ _
    " [OPEN LINES$]. [Source Domain], " & _
    " [OPEN LINES$].[Ship Qty], [OPEN LINES$].[Date Created] " & _
    " FROM [OPEN LINES$], [Back Orders$] " & _
    " WHERE [OPEN LINES$].[Part Number] = [Back Orders$].[Part Number] " & _
    " ORDER BY [OPEN LINES$].[Part Number]", cn
You don't need the ending semicolon.

Here's a simpler example (tested in XL2010)
Sub SqlJoin()

    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim sPath
    Dim sSQL As String

    sSQL = "select a.blah from <t1> a, <t2> b where a.blah = b.blah"

    sSQL = Replace(sSQL, "<t1>", Rangename(Sheet1.Range("A1:A5")))
    sSQL = Replace(sSQL, "<t2>", Rangename(Sheet1.Range("C1:C3")))

    If ActiveWorkbook.Path <> "" Then
      sPath = ActiveWorkbook.FullName
    Else
      MsgBox "Workbook being queried must be saved first..."
      Exit Sub
    End If

    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
                 "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

    oRS.Open sSQL, oConn

    If Not oRS.EOF Then
        Sheet1.Range("E1").CopyFromRecordset oRS
    Else
        MsgBox "No records found"
    End If

    oRS.Close
    oConn.Close

End Sub

Function Rangename(r As Range) As String
    Rangename = "[" & r.Parent.Name & "$" & _
                r.Address(False, False) & "]"
End Function


                        
这篇关于如何在Excel中使用SQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！