Question

如何获取范围值从使用SQL访问的其他表到VB.Net中的DataGridView？

是否可以获取一系列值或者我必须更改工资单决策表中的字段，请指导我。

还有其他解决方案吗？

Public Sub jokenselect(ByVal sql As String)
    Try
        con.Open()
        With cmd
            .Connection = con
            .CommandText = sql
        End With

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
    End Try
    con.Close()
    da.Dispose()
End Sub

Private Sub RetrieveData()
    jokenselect("
SELECT 
EC.EMPID
, E.NAME
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria), #,##0.00 ) as SKILL
, format(EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria), #,##0.00 ) as EXPERIENCE
, format(EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria), #,##0.00 ) as APPEARANCE
, format(EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria), #,##0.00 ) as EDUCATION
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria), #,##0.00 ) as TOTAL CPI 
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID 
ORDER BY 7 DESC")
    filltable(DataGridView2, "EmpPic")
    lbltotalemployee.Text = DataGridView1.RowCount
End Sub

我发布的sql代码的结果

NAME	SKILL	EXPERIENCE	APPEARANCE	EDUCATION	TOTAL CPI
TEST1	145.80	83.91	109.80	129.60	469.11
TEST2	121.50	109.73	85.40	113.40	430.03

工资决定表（tblPayrollDecision）

RANGECPI	RANGESALARY
451-470	11000000 - 12000000
431-450	10000000 - 11000000
411-430	8000000 - 10000000

所需的sql代码结果，但未使用tblPayrollDecision中的“RangeSalary”更新

 jokenselect("
SELECT
EC.EMPID
,E.[NAME]
,format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria), #,##0.00 ) as TOTAL
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID
ORDER BY 3 DESC")

期望的结果

NAME	TOTAL CPI	RANGESALARY
TEST1	469.11	11000000 - 12000000
TEST2	430.03	10000000 - 11000000

Answer 1

由于RANGECPI值是文本字符串，而不是数字，因此比较复杂。如果范围限制在单独的字段中，SQL可以使用SQL联接表B范围内的表A值，因此需要将RANGECPI字符串解析为数值。

使用查询结果作为源，考虑：

SELECT [Name], TotalCPI, RangeSalary FROM query AS E INNER JOIN
    (SELECT Left(RangeCPI,3) AS Start, Mid(RangeCPI,5,3) AS End, RangeSalary 
     FROM tblEmployeeCriteria) AS EC
ON E.TotalCPI >= EC.Start AND E.TotalCPI <= EC.End;

请注意，无法在设计视图中构建/查看此查询-Access将拒绝图形显示中的JOIN语法。

友情链接