SQL多表关联问题：Quotes表多idMachine字段关联Machines表取值异常如何解决

问题原因 #

你当前的查询逻辑存在错误，INNER JOIN 后用 AND 同时关联6个设备ID字段的写法，相当于要求同一个设备ID同时匹配报价单里的6个设备ID字段，只有当某条报价的6个设备ID完全一致时才会返回结果，否则就会无数据或者返回错误的重复值，完全不符合业务需求。

正确实现方案 #

推荐使用多次LEFT JOIN的方式，给每个关联的设备表单独设置别名，分别取出对应位置的设备名称，写法直观易理解：

SELECT 
  q.QuoteID, -- 按需补充你需要展示的报价单其他字段，比如报价编号、日期等
  m1.NameM AS MachineName1,
  m2.NameM AS MachineName2,
  m3.NameM AS MachineName3,
  m4.NameM AS MachineName4,
  m5.NameM AS MachineName5,
  m6.NameM AS MachineName6
FROM Quotes q
LEFT JOIN Machines m1 ON q.idMachine1 = m1.idMachine
LEFT JOIN Machines m2 ON q.idMachine2 = m2.idMachine
LEFT JOIN Machines m3 ON q.idMachine3 = m3.idMachine
LEFT JOIN Machines m4 ON q.idMachine4 = m4.idMachine
LEFT JOIN Machines m5 ON q.idMachine5 = m5.idMachine
LEFT JOIN Machines m6 ON q.idMachine6 = m6.idMachine

用LEFT JOIN而非INNER JOIN的原因是，当某条报价的某个设备ID字段为空（比如只关联了3台设备）时，对应的设备名称会返回NULL，不会把整条报价过滤掉，符合实际业务场景。

修改后的VB代码 #

Sub FillData()        
    Dim sql As String
    ' 可按需补充Quotes表的其他需要展示的字段
    sql = "SELECT 
            q.QuoteID,
            q.QuoteDate,
            m1.NameM AS MachineName1,
            m2.NameM AS MachineName2,
            m3.NameM AS MachineName3,
            m4.NameM AS MachineName4,
            m5.NameM AS MachineName5,
            m6.NameM AS MachineName6
          FROM Quotes q
          LEFT JOIN Machines m1 ON q.idMachine1 = m1.idMachine
          LEFT JOIN Machines m2 ON q.idMachine2 = m2.idMachine
          LEFT JOIN Machines m3 ON q.idMachine3 = m3.idMachine
          LEFT JOIN Machines m4 ON q.idMachine4 = m4.idMachine
          LEFT JOIN Machines m5 ON q.idMachine5 = m5.idMachine
          LEFT JOIN Machines m6 ON q.idMachine6 = m6.idMachine"

    Try
        Dim table As New DataTable
        adapter = New SqlDataAdapter(sql, GetConnection)
        adapter.Fill(table)
        dgvQuote.DataSource = table
        ' 如果需要自动生成列测试效果，可将下面这句改为True，确认效果后手动配置列再设为False
        dgvQuote.AutoGenerateColumns = True
    Catch ex As Exception
        MsgBox("出现错误：" + ex.ToString)
    End Try
End Sub

可选扩展方案 #

如果你需要把6个设备名合并成单个字段展示，可使用SQL的字符串拼接函数，以SQL Server为例：

SELECT 
  q.QuoteID,
  CONCAT_WS('、', m1.NameM, m2.NameM, m3.NameM, m4.NameM, m5.NameM, m6.NameM) AS AllMachineNames
FROM Quotes q
LEFT JOIN Machines m1 ON q.idMachine1 = m1.idMachine
LEFT JOIN Machines m2 ON q.idMachine2 = m2.idMachine
LEFT JOIN Machines m3 ON q.idMachine3 = m3.idMachine
LEFT JOIN Machines m4 ON q.idMachine4 = m4.idMachine
LEFT JOIN Machines m5 ON q.idMachine5 = m5.idMachine
LEFT JOIN Machines m6 ON q.idMachine6 = m6.idMachine

内容的提问来源于stack exchange，提问作者Carlos A. Casco Munive