如何在VB.NET中实现MS Access跨表SQL乘法运算

修改后的SQL语句 #

要实现加权计算，只需在原有计算逻辑基础上，乘以tblWeightingCriteria表中WCSKILL的权重系数（即WCSKILL/100）。由于权重表仅包含一条记录，可通过子查询或交叉连接获取该值：

SELECT 
    EC.EMPID,
    E.NAME,
    (EC.SKILL/(SELECT MIN(MINSKILL.SKILL) FROM tblEmployeeCriteria MINSKILL)*100) * 
    (SELECT WCSKILL/100 FROM tblWeightingCriteria) AS SKILL
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID

也可以用交叉连接实现（更直观）：

SELECT 
    EC.EMPID,
    E.NAME,
    (EC.SKILL/(SELECT MIN(MINSKILL.SKILL) FROM tblEmployeeCriteria MINSKILL)*100) * 
    (WC.WCSKILL/100) AS SKILL
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID
CROSS JOIN tblWeightingCriteria WC

计算逻辑验证 #

以Tamendran的SKILL值为例：
90（EC.SKILL） / 50（tblEmployeeCriteria的最小SKILL） * 100 * (81（WCSKILL）/100) = 145.80，与期望结果完全匹配。

VB.NET中执行该SQL的示例代码 #

使用OleDb连接Access数据库执行上述SQL：

Imports System.Data.OleDb

Public Sub CalculateWeightedSkill()
    ' 替换为你的Access数据库实际路径
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDatabase.accdb;"
    
    Dim sqlQuery As String = "SELECT EC.EMPID,E.NAME,(EC.SKILL/(SELECT MIN(MINSKILL.SKILL) FROM tblEmployeeCriteria MINSKILL)*100)*(SELECT WCSKILL/100 FROM tblWeightingCriteria) AS SKILL FROM tblEmployeeCriteria EC INNER JOIN tblemployee E ON EC.EMPID = E.EMPID"
    
    Using conn As New OleDbConnection(connString)
        conn.Open()
        Using cmd As New OleDbCommand(sqlQuery, conn)
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine($"EMPID: {reader("EMPID")}, NAME: {reader("NAME")}, SKILL: {Math.Round(CDbl(reader("SKILL")), 2)}")
                End While
            End Using
        End Using
    End Using
End Sub

注意事项 #

• 若使用旧版.mdb格式的Access文件，需将连接字符串中的Microsoft.ACE.OLEDB.12.0替换为Microsoft.Jet.OLEDB.4.0。
• 若权重表存在多条记录，需添加WHERE条件筛选目标权重（例如WHERE ID=1）。

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