主题：获取不重复的对象属性后，怎么根据对象属性值去对应的遍历表中是否有符合条件的列，如果有就对应的计算值

 获取不重复的对象属性后，怎么根据对象属性值去对应的遍历表中是否有符合条件的列，如果有就对应的计算值

 
此主题相关图片如下：11.png

当前代码如下：

Dim cmd As New SQL Command
cmd.Con necti
cmd.Comma ndText ="Sele ct Count(*) From {巡察工作安排}"
Dim dt As DataTable
dt = cmd.ExecuteReader()
Dim Products As List(Of String)
Products = dt.sqlGetValues("巡察对象属性")
Dim time As String =Date.today.Year
output.show(time)
For Each Product As String In Products
    For Each c As DataCol In dt.dataCols
        output.show(c.name)
        Output.Show(Product)
        cmd.CommandText ="Sel ect Count(*) From {巡察对象信息} Where 巡察对象属性 = '" & Product & "'"
        If cmd.ExecuteScalar > 0 Then
            output.show(cmd.ExecuteScalar)
        End If
        cmd.CommandText ="Sel ect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "'"
        If cmd.ExecuteScalar > 0 Then
            output.show(cmd.ExecuteScalar)
        End If
    Next
Next

输出值：

2022
Column1
党政机关
2
4

 
此主题相关图片如下：11.png

想实现红色部分的值通过Product 来对应到列名  并赋值给对应的列下   然后对应汇总  并根据年度值 按年度生成对应的数据行

Dim cmd As New SQLCommand
cmd.C
cmd.CommandText ="Sel ect Count(*) From {巡察工作安排}"
Dim dt As DataTable
dt = cmd.ExecuteReader()
Dim Products As List(Of String)
Products = dt.sqlGetValues("巡察对象属性")
Dim time As String =Date.today.Year
Dim y As Integer = time '指定年份
Dim time1 As New Date(y, 1, 1)
Dim time2 As New Date(y, 12, 31)
For Each Product As String In Products
    cmd.CommandText ="Sele ct * From {巡察工作覆盖情况}"
    Dim dt1 As DataTable
    dt1 = cmd.ExecuteReader()
    'Output.Show(Product)
    For Each dc As DataCol In dt1.DataCols
        output.show(dc.name)
        If dc.name.Contains(Product) Then
            If dc.name.Contains(Product & "_应巡数") Then
                cmd.CommandText ="Se lect Count(*) From {巡察对象信息} Where 巡察对象属性 = '" & Product & "'"
                If cmd.ExecuteScalar > 0 Then
                    output.show(cmd.ExecuteScalar)
                End If
            End If
            If dc.name.Contains(Product & "_已巡数") Then
                cmd.CommandText ="Sel ect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "'"
                If cmd.ExecuteScalar > 0 Then
                    output.show(cmd.ExecuteScalar)
                End If
            End If
        If dc.name.Contains(Product & "_本年度") Then
                cmd.CommandText ="Sel ect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "' and 巡察开始时间 >= '" & time1 & "' And 巡察开始时间 <= '" & time2 & "'"
                If cmd.ExecuteScalar > 0 Then
                    output.show(cmd.ExecuteScalar)
                End If
            End If
        End If
    Next
Next

输出得到：

党政机关
guid
创建人guid
创建日期
编辑人guid
编辑日期
巡察届别
巡察开始时间
巡察结束时间
巡察轮次
党政机关_应巡数
2
党政机关_已巡数
4

党政机关_本年度
4
党政机关_本年度
事业单位_应巡数
事业单位_已巡数
事业单位_本年度
国有企业_应巡数
国有企业_已巡数
国有企业_本年度
乡镇街道_应巡数
乡镇街道_已巡数
乡镇街道_本年度
其他对象_应巡数
其他对象_已巡数
其他对象_本年度
总计_应巡数
总计_已巡数
总计_覆盖率
村级党组织_应巡数
村级党组织_已巡数
村级党组织_本年度
村级党组织_覆盖率
提级巡察
党委常委研究巡察工作次数

已初步实现，老师有没有办法简化代码提高运行效率呢？

当前代码如下：

Dim cmd As New SQLCommand
cmd.Connecti
cmd.CommandText ="Sel ect * From {巡察工作安排}"
Dim dt As DataTable
dt = cmd.ExecuteReader()
Dim Products As List(Of String)
Products = dt.sqlGetValues("巡察对象属性")
Dim time As String =Date.today.Year
Dim y As Integer = time '指定年份
Dim time1 As New Date(y, 1, 1)
Dim time2 As New Date(y, 12, 31)

cmd.CommandText ="Sel ect * From {巡察工作覆盖情况}"
Dim dt1 As DataTable
dt1 = cmd.ExecuteReader(True)
Dim bb As DataRow = dt1.AddNew

Dim yxs As Integer
Dim yxs1 As Integer
Dim bnd As Integer
For Each Product As String In Products
   
    Output.Show(Product)
    For Each dc As DataCol In dt1.DataCols
        output.show(dc.name)
        If dc.name.Contains(Product) Then
            If dc.name.Contains(Product & "_应巡数") Then
                cmd.CommandText ="Sel ect Count(*) From {巡察对象信息} Where 巡察对象属性 = '" & Product & "'"
                If cmd.ExecuteScalar > 0 Then
                    bb(Product & "_应巡数")=cmd.ExecuteScalar
                    'cmd.CommandText = "UPD ATE {巡察工作覆盖情况} SET " & Product & "_应巡数" & "= '"& cmd.ExecuteScalar & "'"
                    'cmd.ExecuteNonQuery()
                    yxs= yxs +  cmd.ExecuteScalar
                End If
            End If
            If dc.name.Contains(Product & "_已巡数") Then
                cmd.CommandText ="Sel ect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "'"
                If cmd.ExecuteScalar > 0 Then
                    bb(Product & "_已巡数")=cmd.ExecuteScalar
                    'cmd.CommandText = "UPD ATE {巡察工作覆盖情况} SET " & Product & "_已巡数" & "= '"& cmd.ExecuteScalar & "'"
                    'cmd.ExecuteNonQuery()
                    yxs1= yxs1 +  cmd.ExecuteScalar
                End If
            End If
            If dc.name.Contains(Product & "_本年度") Then
                cmd.CommandText ="Se lect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "' and 巡察开始时间 >= '" & time1 & "' And 巡察开始时间 <= '" & time2 & "'"
                If cmd.ExecuteScalar > 0 Then
                    bb(Product & "_本年度")=cmd.ExecuteScalar
                    'cmd.CommandText = "UP DATE {巡察工作覆盖情况} SET " & Product & "_本年度" & "= '"& cmd.ExecuteScalar & "'"
                    'cmd.ExecuteNonQuery()
                    bnd= bnd+  cmd.ExecuteScalar
                End If
            End If
        End If
        output.show(yxs)
        bb("总计_应巡数")=yxs
        bb("总计_已巡数")=yxs1
        bb("总计_覆盖率")=bnd
    Next
    bb.save
Next

For Each Product As String In Products
    Output.Show(Product)
    For Each dc As DataCol In dt1.DataCols
        output.show(dc.name)
        If dc.name.Contains(Product) Then
            If dc.name.Contains(Product & "_应巡数") Then
                cmd.CommandText ="Sel ect Count(*) From {巡察对象信息} Where 巡察对象属性 = '" & Product & "' "
                If cmd.ExecuteScalar > 0 Then
                    bb(Product & "_应巡数")=cmd.ExecuteScalar
                    yxs= yxs +  cmd.ExecuteScalar
                    bb("总计_应巡数")=yxs
                End If
            End If
            If dc.name.Contains(Product & "_已巡数") Then
                cmd.CommandText ="Se lect Count(*) From {巡察工作安排} "
                Dim dt11 As DataTable
                dt11 = cmd.ExecuteReader()
                Dim Ps As List(Of String)
                Ps = dt11.sqlGetValues("巡察对象")
                For Each P As String In Ps
                    cmd.CommandText ="Sel ect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "' and 巡察对象 = '" & P & "'"
                    If cmd.ExecuteScalar > 0 Then
                        output.show(cmd.ExecuteScalar)  得到的数据是3，想进行筛选，对于同一个对象属性和巡察对象值得数据   只统计一次  预期统计数据为2  怎么操作呢？
                        bb(Product & "_已巡数")=cmd.ExecuteScalar
                        yxs1= yxs1 +  cmd.ExecuteScalar
                        bb("总计_已巡数")=yxs1
                    End If
                Next
            End If
           
            If dc.name.Contains(Product & "_本年度") Then
                Dim cmd2 As SQLCommand = rq.cmd
                cmd2.CommandText ="Se lect Count(*) From {巡察工作安排} Where 巡察对象属性 = '" & Product & "' and 巡察开始时间 >= '" & time1 & "' And 巡察开始时间 <= '" & time2 & "'"
                If cmd2.ExecuteScalar > 0 Then
                    bb(Product & "_本年度")=cmd2.ExecuteScalar
                    bnd= bnd+  cmd.ExecuteScalar
                    bb("总计_本年度")=bnd
                End If
            End If
        End If
    Next
    bb("总计_覆盖率")= Format(bb("总计_应巡数")/bb("总计_已巡数"),"0.00%")
    bb("村级党组织_覆盖率")= Format(bb("村级党组织_应巡数")/bb("村级党组织_已巡数"),"0.00%")
    bb.save
Next

比如巡察工作安排表中有如下记录：

巡察对象属性     巡察对象

事业单位           某某医院

企业单位           某某投资工资

企业单位           某某投资工资

统计数据的话  就只能算两个数据   不能计算为三个数据