如何在数据表合并行时在多个列中的数据相匹配？ [英] How to merge rows in a DataTable when data in multiple columns match?

问题描述

我试图找出数据表中的列1时，列2，栏3，column4匹配行合并的好方法。有没有人有如何能够在VB.NET和/或C＃来完成指针或想法？

  DataTable中有重复的行合并
 ---------------- -------------------------------------------------- 
 | COLUMN1 |列2 |栏3 | Column4 | Column5 | Column6 | 
 ---------------------------------------------- --------------- 
 | 123456 | 6 | 54 | 5 | 0.00 | 36.78 | 
 | 123456 | 6 | 54 | 5 | 21.00 | 0.00 | 
 | 123456 | 6 | 54 | 8 | 0.00 | 102.09 | 
 | 123456 | 6 | 54 | 8 | 6.50 | 0.00 | 
 
 
最终数据表与合并后的行
 ----------------------------- -------------------------------- 
 | COLUMN1 |列2 |栏3 | Column4 | Column5 | Column6 | 
 ---------------------------------------------- --------------- 
 | 123456 | 6 | 54 | 5 | 21.00 | 36.78 | 
 | 123456 | 6 | 54 | 8 | 6.50 | 102.09 | 
  

解决方案

下面是一个非LINQ的选择。它的作用是循环每行中的第一个表。然后检查次要表，看是否有其符合条件的任何行。如果有，它增加了在其他列中的值。如果没有，它增加了整个行到新表。

  //克隆（）只克隆表结构。它不也克隆中的数据。 
的DataTable dtFinal = dtOriginal.Clone（）; 
的for（int i = 0; I< dtOriginal.Rows.Count;我++）
 {
布尔isDupe = FALSE; 
表示（中间体J = 0; J&下; dtFinal.Rows.Count; J ++）
 {
如果（dtOriginal.Rows [I] [0]的ToString（）== dtFinal。行[J] [0]的ToString（）
和;&安培; dtOriginal.Rows [I] [1]的ToString（）== dtFinal.Rows [J] [1]的ToString（）
和;&安培; dtOriginal.Rows [I] [2]的ToString（）== dtFinal.Rows [J] [2]的ToString（））
 {
 dtFinal.Rows [J]。[ 3] = int.Parse（dtFinal.Rows [j]的[3]的ToString（））+ int.Parse（dtOriginal.Rows [Ⅰ] [3]的ToString（））; 
 isDupe = TRUE; 
中断; 
} 
} 
 
如果
 {
 dtFinal.ImportRow（dtOriginal.Rows [I]）（isDupe！）; 
} 
} 
  

您可以在此扩大到包括更多/更少列在匹配准则，你的另外的逻辑。你很可能也想到了什么，以摆脱列号硬编码如迭代他们到一个特定的指数之类的东西。这一切都取决于你的需求。这应该会给虽然你一个像样的起点。

I am trying to figure out a good way to merge rows in a DataTable when column1, column2, column3, column4 match. Does anyone have pointers or ideas of how this can be accomplished in VB.NET and/or C#?

DataTable with duplicate rows to merge 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 0.00    | 36.78   |
| 123456  | 6       | 54      | 5       | 21.00   | 0.00    |
| 123456  | 6       | 54      | 8       | 0.00    | 102.09  |
| 123456  | 6       | 54      | 8       | 6.50    | 0.00    |


Final DataTable with merged rows 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 21.00   | 36.78   |
| 123456  | 6       | 54      | 8       | 6.50    | 102.09  |

解决方案

Here is a non-LINQ alternative. What it does is iterate each row in the first table. It then checks a secondary table to see if there are any rows in it that match the criteria. If there are, it adds the values in the other columns. If there aren't, it adds the entire row to the new table.

// Clone() only clones the table structure. It does not also clone the data.
DataTable dtFinal = dtOriginal.Clone();
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
    bool isDupe = false;
    for (int j = 0; j < dtFinal.Rows.Count; j++)
    {
        if (dtOriginal.Rows[i][0].ToString() == dtFinal.Rows[j][0].ToString()
            && dtOriginal.Rows[i][1].ToString() == dtFinal.Rows[j][1].ToString()
            && dtOriginal.Rows[i][2].ToString() == dtFinal.Rows[j][2].ToString())
        {
            dtFinal.Rows[j][3] = int.Parse(dtFinal.Rows[j][3].ToString()) + int.Parse(dtOriginal.Rows[i][3].ToString()); 
            isDupe = true;
            break;
        }
    }

    if (!isDupe)
    {
        dtFinal.ImportRow(dtOriginal.Rows[i]);
    }
}

You could expand upon this to include more/less columns in your matching criteria and your addition logic. You could probably also think of something to get rid of the column number hardcoding such as iterating them up to a specific index or something. It all depends on your requirements. This should give you a decent starting point though.

这篇关于如何在数据表合并行时在多个列中的数据相匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！