使用vb.net计算数据表中重复的行数

Question

我有一个名为dtstore的数据表，其中有4列名为section, department, palletnumber and uniquenumber。我正在尝试创建一个名为dtmulti的新数据表，它有一个额外的列，名为multi，它显示重复行数的计数.

dtstore

section | department | palletnumber | batchnumber

---------------------------------------------------

 pipes      2012          1234           21

 taps       2011          5678           345

 pipes      2012          1234           21

 taps       2011          5678           345

 taps       2011          5678           345

 plugs      2009          7643           63


dtmulti

section | department | palletnumber | batchnumber | multi

----------------------------------------------------------

 pipes      2012          1234           21           2

 taps       2011          5678           345          3

我尝试过很多方法，但是我的代码总是觉得笨拙和臃肿，有什么有效的方法吗？

下面是我使用的代码：

Dim dupmulti = dataTable.AsEnumerable().GroupBy(Function(i) i).Where(Function(g) g.Count() = 2).Select(Function(g) g.Key)  

For Each row In dupmulti multirow("Section")  = dup("Section") 

multirow("Department") = dup("Department") 
multirow("PalletNumber") = dup("PalletNumber") 
multirow("BatchNumber") = dup("BatchNumber") 
multirow("Multi") = 2
    Next

Answer 1

以下代码的假设:包含原始信息的DataTable称为dup。它可能包含任意数量的重复项，所有这些都可以通过查看第一列来定义。

'Creating final table from the columns in the original table
Dim multirow As DataTable = New DataTable

For Each col As DataColumn In dup.Columns
   multirow.Columns.Add(col.ColumnName, col.DataType)
Next
multirow.Columns.Add("multi", GetType(Integer))

'Looping though the groupped rows (= no duplicates) on account of the first column
For Each groups In dup.AsEnumerable().GroupBy(Function(x) x(0))

    multirow.Rows.Add()

    'Adding all the cells in the corresponding row except the last one
    For c As Integer = 0 To dup.Columns.Count - 1
        multirow(multirow.Rows.Count - 1)(c) = groups(0)(c)
    Next

    'Adding the last cell (duplicates count) 
    multirow(multirow.Rows.Count - 1)(multirow.Columns.Count - 1) = groups.Count

Next