主题：在交叉统计设置中复制代码 不能直接使用吗？

剩余数量，直接用【领用】-【使用】即可。

 

试试

 

Dim cmd As new SQLCommand
cmd.commandText = "Select  领取人, format(日期,'yyyy年MM月') as 日期, {登记表}.产品id号 As 产品id号 From {订单} Left JOIN {登记表} ON {登记表}.[产品ID号] = {订单}.[产品ID号]"
Dim dt As DataTable = cmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim str As String = "未领取,报废,返修A厂"
Dim prds As List(of String) = dt.GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
prds.Insert(0, "返修A厂")
prds.Insert(0, "报废")
prds.Insert(0, "未领取")
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
Next
dtb.Build()
For Each cus As String In dt.GetValues("日期")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 = '" & cus & "'"
    For Each prd As String In prds
        dr(prd & "_领取数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
        dr(prd & "_使用数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "' and 产品id号 is not null")
    Next
Next
MainTable= Tables("统计")

 看21楼，如果还有问题，做个大数据量的例子发上来测试。

Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
索引和长度必须引用该字符串内的位置。
参数名: length
应该如何修改？

 

For Each cus As String In dt.GetValues("日期", "日期 is not null")

 

或者

 

For Each cus As String In dt.GetValues("日期", "len(日期) >= 8")

 回复25楼，做个例子发上来测试。

麻烦帮看下 谢谢

Dim cmd As new SQLCommand
cmd.commandText = "Select  领取人, format(日期,'yyyy年MM月') as 日期, {登记表}.设备ID号 As 设备ID号 From {订单} Left JOIN {登记表} ON {登记表}.[设备ID号] = {订单}.[设备ID号]"
Dim dt As DataTable = cmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim v = e.form.Controls("CheckedComboBox1").value
Dim prds As new List(of String)
Dim str As String = "未领取,报废,返修A厂"
If v > "" Then
    prds.AddRange(v.split(","))
Else
    prds = DataTables("订单").GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
    prds.Insert(0, "返修A厂")
    prds.Insert(0, "报废")
    prds.Insert(0, "未领取")
End If
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
Next
dtb.Build()
For Each cus As String In dt.GetValues("日期", "len(日期) >= 8")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 = '" & cus & "'"
    For Each prd As String In prds
        dr(prd & "_领取数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
        dr(prd & "_使用数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "' and 设备ID号 is not null")
    Next
Next
MainTable= Tables("统计")

如果我想实现取值和计数这两个功能呢 知道他领取的都是哪些设备 我看Compute支持计算方面的 我想取值应该用哪个？

 

Dim cmd As new SQLCommand
cmd.commandText = "Select  领取人, format(日期,'yyyy年MM月') as 日期, {登记表}.设备ID号 As 设备ID号 From {订单} Left JOIN {登记表} ON {登记表}.[设备ID号] = {订单}.[设备ID号]"
Dim dt As DataTable = cmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim v = e.form.Controls("CheckedComboBox1").value
Dim prds As new List(of String)
Dim str As String = "未领取,报废,返修A厂"
If v > "" Then
    prds.AddRange(v.split(","))
Else
    prds = DataTables("订单").GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
    prds.Insert(0, "返修A厂")
    prds.Insert(0, "报废")
    prds.Insert(0, "未领取")
End If
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
    dtb.AddDef(prd & "_值", Gettype(String))
Next
dtb.Build()
For Each cus As String In dt.GetValues("日期", "len(日期) >= 8")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 = '" & cus & "'"
    For Each prd As String In prds
        dr(prd & "_领取数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
        dr(prd & "_使用数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "' and 设备ID号 is not null")
        dr(prd & "_值") = dt.GetComboListString("设备id号", filter & " And [领取人] = '" & prd & "' and 设备ID号 is not null")
       
    Next
Next
MainTable= Tables("统计")