VB.NET中Access数据库多表联合查询及客户表关联实现问询

当前UNION写法存在两个核心问题：

• SELECT *会导致SALES与SALESDETAIL的列结构不匹配（两张表的字段数量、类型不一致），而UNION要求两边查询的列数、对应位置的字段类型必须完全一致；
• 未关联CUSTOMER表实现CODECUS到NAMECUST的替换。

修正后的完整代码 #

Dim Path As String = Environment.CurrentDirectory
Dim cn As String = "provider=Microsoft.ACE.OLEDB.12.0; data source=" & Path & "\ORDERS.accdb;"
Dim source1 As New BindingSource()

Private Sub fillDataGridView1()
    Try
        Dim query As String = "SELECT " & _
                               "S.NUMORD, " & _
                               "C.NAMECUST AS 客户名称, " & _
                               "S.DATEORD, " & _
                               "S.TOTALAMT, " & _
                               "NULL AS CODEPROD, " & _
                               "NULL AS PRICEUNIT, " & _
                               "NULL AS QUANTITY, " & _
                               "NULL AS SUBTOTAL " & _
                               "FROM SALES S " & _
                               "LEFT JOIN CUSTOMER C ON S.CODECUS = C.CODECUS " & _
                               "UNION ALL " & _
                               "SELECT " & _
                               "SD.NUMORD, " & _
                               "NULL AS 客户名称, " & _
                               "NULL AS DATEORD, " & _
                               "NULL AS TOTALAMT, " & _
                               "SD.CODEPROD, " & _
                               "SD.PRICEUNIT, " & _
                               "SD.QUANTITY, " & _
                               "SD.SUBTOTAL " & _
                               "FROM SALESDETAIL SD"
        Using con As OleDbConnection = New OleDbConnection(cn)
            Using cmd As OleDbCommand = New OleDbCommand(query, con)
                Using da As New OleDbDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    source1.DataSource = dt
                    Me.DataGridView1.DataSource = source1
                    Me.DataGridView1.Refresh()
                End Using
            End Using
        End Using
    Catch ex As Exception
        ' 添加异常提示便于排查问题
        MessageBox.Show("数据加载失败：" & ex.Message)
    End Try
End Sub

关键说明 #

• 使用LEFT JOIN关联CUSTOMER，确保即使SALES表中存在CUSTOMER未收录的CODECUS记录，订单数据也不会丢失；
• 采用UNION ALL而非UNION，避免不必要的去重操作，提升查询性能；若需去重可换回UNION；
• 手动对齐两张表的查询列，SALESDETAIL中没有的字段用NULL填充，满足UNION的语法要求；
• 给NAMECUST字段设置别名客户名称，让DataGridView的表头更直观。

内容的提问来源于stack exchange，提问作者roy