专家坐堂-合并求和，删除多余行Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  合并求和，删除多余行  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=95960)

--  作者：huhu
--  发布时间：2017/2/9 16:00:00
--  合并求和，删除多余行
库位编码，物料编码，库位类别，订单号，体态相等他们有很多行，其实都是重复的。
只需要保留一行就行。库存数量求和就行。这个怎么做快?

此主题相关图片如下：相同行合并为一行.png

--  作者：有点色
--  发布时间：2017/2/9 16:04:00
--

删除重复，参考代码

Dim idx As String = "-1,"
Dim idx_temp As String = ""
Dim pdr As DataRow = Nothing
Dim count As Integer = 0
Dim cs As String = "第一列,第二列"
For Each dr As DataRow In DataTables("表A").Select("", cs)
    Dim flag As Boolean = False
    If pdr IsNot Nothing Then
        For Each c As String In cs.split(",")
            If pdr(c).replace(" ","") <> dr(c).replace(" ", "") Then
                flag = True
                Exit For
            End If
        Next
        If flag Then
            If count > 1 Then
                idx &= idx_temp
            End If
            count = 1
            idx_temp = ""
        Else
            count += 1
            idx_temp &= dr("_Identify") & ","
        End If
    Else
        count += 1
    End If
    pdr = dr
Next

If count > 1 Then
    idx &= idx_temp
End If

Tables("表A").Filter = "_Identify not In (" & idx.trim(",") & ")"

\'DataTables("表A").DeleteFor("_Identify not In (" & idx.trim(",") & ")")

[此贴子已经被作者于2017/2/9 16:05:19编辑过]

--  作者：有点蓝
--  发布时间：2017/2/9 16:05:00
--
按重复数据的列，对数量做一个分组统计，生成统计表，删除原表数据，把统计表数据填充回到原表

http://www.foxtable.com/webhelp/scr/0680.htm

--  作者：huhu
--  发布时间：2017/2/9 16:29:00
--
看来一遍，咋就没对库存数量做求和呢?
Dim idx As String = "-1,"
Dim idx_temp As String = ""
Dim pdr As DataRow = Nothing
Dim count As Integer = 0
Dim cs As String = "物料编码,库位编码,订单号,订单行号,库位类别,体态"
For Each dr As DataRow In DataTables("abc").S elect("", cs)
Dim flag As Boolean = False
If pdr IsNot Nothing Then
For Each c As String In cs.split(",")
If pdr(c).replace(" ","") <> dr(c).replace(" ", "") Then
flag = True
Exit For
End If
Next
If flag Then
If count > 1 Then
idx &= idx_temp
End If
count = 1
idx_temp = ""
Else
count += 1
idx_temp &= dr("_Identify") & ","
End If
Else
count += 1
End If
pdr = dr
Next
If count > 1 Then
idx &= idx_temp
End If

Tables("abc").Filter = "_Identify not In (" & idx.trim(",") & ")"

--  作者：huhu
--  发布时间：2017/2/9 17:10:00
--
哦。看到了，用分组统计简单。

--  作者：有点色
--  发布时间：2017/2/9 17:25:00
--

Dim idx As String = "-1,"
Dim idx_temp As String = ""
Dim pdr As DataRow = Nothing
Dim count As Integer = 0
Dim cs As String = "第一列,第二列"
Dim sum As Double = 0
Dim drs As List(Of DataRow) = DataTables("表A").Select("", cs)
Dim fdr As DataRow = drs(0)
For Each dr As DataRow In drs
    Dim flag As Boolean = False
    If pdr IsNot Nothing Then
        For Each c As String In cs.split(",")
            If pdr(c).replace(" ","") <> dr(c).replace(" ", "") Then
                flag = True
                Exit For
            End If
        Next
        If flag Then
            output.show(dr("第一列") & dr("第二列") & fdr("第一列") & fdr("第二列") & " " & sum)
            fdr("第六列") = sum
            If count > 1 Then
                idx &= idx_temp
            End If
            fdr = dr
            sum = 0
            count = 1
            idx_temp = ""
        Else
            count += 1
            idx_temp &= dr("_Identify") & ","
        End If
    Else
        count += 1
    End If
    sum += dr("第五列")
    pdr = dr
Next

If count > 1 Then
    idx &= idx_temp
End If

fdr("第六列") = sum

Tables("表A").Filter = "_Identify not In (" & idx.trim(",") & ")"

\'DataTables("表A").DeleteFor("_Identify not In (" & idx.trim(",") & ")")

--  作者：playmal
--  发布时间：2021/3/6 18:11:00
--
经测试，2楼的代码只能去掉两个其中的一个重复项，如果有多个重复项，不能只保留一个，去掉其他的多个。请版主测试一下。