时间戳

首页 > 解决方案 > 一个datagridview中两个SQL表的比较

vb.net - 一个datagridview中两个SQL表的比较

问题描述

我想比较两个不同的 SQL 表Esimation和Actual Cost. 两个表的列/行相同。

如何将一个datagridview中显示的两个表作为第一列中SQL表的列名作为行值和Estimation作为Actual CostGridview的列名进行比较。

示例图像

标签： vb.net

解决方案

最简单的方法是连接，但正如您在上面评论的那样，它提供了一个水平网格并且您希望它垂直。

所以我认为这种方法应该有效，尽管我不确定它的效率如何，所以你需要根据你的数据集自己测试它。

select 'Material' as Description, Estimation.Material as Estimation, ActualCost.Material as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo
UNION 
select 'Fabrication' as Description, Estimation.Fabrication as Estimation, ActualCost.Fabrication as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo
UNION 
select 'Transporation' as Description, Estimation.Transporation as Estimation, ActualCost.Transporation as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo
UNION 
select 'OH' as Description, Estimation.OH as Estimation, ActualCost.OH as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo
UNION 
select 'Profit' as Description, Estimation.Profit as Estimation, ActualCost.Profit as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo

我没有运行该查询，所以不能保证它是 100%，但希望它能让你走上正确的轨道

如果您出于任何原因不想使用 UNION SQL 命令，您可以在 VB 中像这样做同样的事情（虽然有点笨拙）

Dim dt As New DataTable
Using da As New SqlDataAdapter("select 'Material' as Description, Estimation.Material as Estimation, ActualCost.Material as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo", New SqlConnection(yourConnectionString))
        da.SelectCommand.Parameters.AddWithValue("JobNo", YourJobNo)
        da.Fill(dt)
        da.SelectCommand.CommandText = "select 'Fabrication' as Description, Estimation.Fabrication as Estimation, ActualCost.Fabrication as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo"
        da.Fill(dt)
        da.SelectCommand.CommandText = "select 'Transporation' as Description, Estimation.Transporation as Estimation, ActualCost.Transporation as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo"
        da.Fill(dt)
        da.SelectCommand.CommandText = "select 'OH' as Description, Estimation.OH as Estimation, ActualCost.OH as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo"
        da.Fill(dt)
        da.SelectCommand.CommandText = "select 'Profit' as Description, Estimation.Profit as Estimation, ActualCost.Profit as ActualCost from ActualCost INNER JOIN Estimation on ActualCost.JobNo = Estimation.JobNo Where ActualCost.JobNo=@JobNo"
        da.Fill(dt)
End Using

推荐阅读