如何比较2个数据表并在C＃中返回重复值 [英] How to compare 2 data tables and return duplicate values in C#

问题描述

我有一个要求，其中我有两个数据表，两个都有不同的列集，行是动态的。我想将一个数据表的第5列中的值与第二个数据表中的第4列进行比较，并返回一个仅存在匹配值的新数据表。



数据表是system.Data.Datatable



我是一个完整的初学者，你的帮助对我来说很棒。



数据表1如下所示

I have a requirement in which i have two datatables, both with different sets of columns, rows are dynamic. I want to compare the values in 5th column of one datatable with the 4th column on the 2nd Datatable and return a new data table on which only the matched values are present.

The data tables are system.Data.Datatable

I am a complete beginner and your help will be great for me.

Data Table 1 looks like below

xxyy    2   Enjoy 2000 MB                      xxyy 88035
yyxx    2   Enjoy 250 Minutes                  yyxx 88039
yyxx    2   Get 1 GB Data, valid for 3 day.    yyxx 88549
zzyy    2   10 GB Data   valid for 30 days.    zzyy 88535

数据表2如下

Data table 2 like below

2   Enjoy 2000 MB                      xxyy 88035 john
2   Enjoy 250 Minutes                  yyxx 88039 george
2   Get 1 GB Data, valid for 3 day.    yyxx 88612 anil
2   10 GB Data   valid for 30 days.    zzyy 88992 peter

这里，我试图比较数据表1的第5列和数据表2的第4列的值，结果应如下所示

Here, i am trying to compare the values on column 5 of data table1 and column 4 of data table 2, the result should be like below

xxyy    2   Enjoy 2000 MB        xxyy   88035
yyxx    2   Enjoy 250 Minutes    yyxx   88039

即如果值是常见的，则返回datatable1并删除唯一值。



我尝试过：



我是一个完全的初学者，不知道从哪里开始

i.e if values are common, return datatable1 with removing the unique values.

What I have tried:

I'm a complete beginner and have no idea where to start

推荐答案

使用IN子查询：



IN（Transact-SQL）| Microsoft Docs [ ^ ]

Use an "IN" sub-query:

IN (Transact-SQL) | Microsoft Docs[^]

如果您想获得匹配的记录，请使用join子句（C＃参考）| Microsoft Docs [ ^ ]



例如：

If you would like to get matched records, use join clause (C# Reference) | Microsoft Docs[^]

For example:

DataTable dt1 = new DataTable();
dt1.Columns.AddRange(new DataColumn[]
	{
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn()
	});
dt1.Rows.Add(new object[]{"xxyy", 2, "Enjoy 2000 MB", "xxyy", 88035});
dt1.Rows.Add(new object[]{"yyxx", 2, "Enjoy 250 Minutes", "yyxx", 88039});
dt1.Rows.Add(new object[]{"yyxx", 2, "Get 1 GB Data, valid for 3 day.", "yyxx", 88549});
dt1.Rows.Add(new object[]{"zzyy", 2, "10 GB Data   valid for 30 days.", "zzyy", 88535});

DataTable dt2 = new DataTable();
dt2.Columns.AddRange(new DataColumn[]
	{
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn()
	});
dt2.Rows.Add(new object[]{2, "Enjoy 2000 MB", "xxyy", 88035, "john"});
dt2.Rows.Add(new object[]{2, "Enjoy 250 Minutes", "yyxx", 88039, "george"});
dt2.Rows.Add(new object[]{2, "Get 1 GB Data, valid for 3 day.", "yyxx", 88612, "anil"});
dt2.Rows.Add(new object[]{2, "10 GB Data   valid for 30 days.", "zzyy", 88992, "peter"});

var result = from d1 in dt1.AsEnumerable()
	join d2 in dt2.AsEnumerable() on new {A = d1[1], B = d1[4]} equals new {A = d2[0], B = d2[3]}
	select d1;
	
foreach (DataRow dr in result)
{
	Console.WriteLine("{0} {1} {2} {3} {4}", dr[0], dr[1], dr[2], dr[3], dr[4]);
}

祝你好运！

Good luck!

//clone structure of dt1 into new table to store the result
DataTable dt3 = dt1.Clone();
//find index numbers in dt1 not present in dt2
var idsExceptDt2 = dt1.AsEnumerable().Select(r => (long)r.ItemArray[4])
    .Except(dt2.AsEnumerable().Select(r => (long)r.ItemArray[3])).ToArray();

foreach (DataRow r in dt1.Rows)
  {
    //does the row index number match any of the non-duplicate index numbers?
    bool isMatched = idsExceptDt2.Any(id => (long)r.ItemArray[4] == id);
    if (!isMatched)
       {
         dt3.Rows.Add(r.ItemArray);
       }
  }

这篇关于如何比较2个数据表并在C＃中返回重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！