如何从2个数据表中仅获取不匹配的数据 [英] How to get only non matched data from 2 datatable

问题描述

hai朋友我有一个概念,,,





i有2个数据表dt1，dt2





dt1包含

A

B

C

D







和dt2



包含



A

B

C

D

E



我想要



只有E



如何获得plz帮助

hai freinds i have a prob ,,,


i have 2 datatables dt1,dt2


dt1 contains
A
B
C
D



and dt2

contain

A
B
C
D
E

I want

only E

how to get plz help

推荐答案

SELECT *
FROM dt2
WHERE field1 NOT IN (SELECT field1 FROM dt1)

这将为您提供表2中不在表1中的所有值。

This will give you all values from table 2 that are not in table 1.

DataTable dt3 = new DataTable（ ）;

dt3.Columns.Add（Name）;

for（int i = 0;我< dt1.Rows.Count; i ++）

{

for（int j = 0; j< dt2.Rows.Count; j ++）

{

if（dt1.Rows [i] [0] .ToString（）== dt2.Rows [j] [0] .ToString（））

{



}

其他

{

DataRow dr = dt3.NewRow（）;

dr [Name] = dt1.Rows [i] [0] .ToString（）;

dt3.Rows.Add（dr）;

}

if（dt2.Rows [i] [0] .ToString（）== dt1.Rows [j] [0] .ToString（））

{< br $>


}

其他

{

DataRow dr = dt3.NewRow（） ;

dr [Name] = dt2.Rows [i] [0] .ToString（）;

dt3.Rows.Add（dr）;

}

}

}









您将获得数据表中的所有数据dt3

DataTable dt3=new DataTable ();
dt3.Columns.Add("Name");
for (int i = 0; i < dt1.Rows.Count; i++)
{
for (int j = 0; j < dt2.Rows.Count; j++)
{
if (dt1.Rows[i][0].ToString() == dt2.Rows[j][0].ToString())
{

}
else
{
DataRow dr = dt3.NewRow();
dr["Name"] = dt1.Rows[i][0].ToString();
dt3.Rows.Add (dr);
}
if (dt2.Rows[i][0].ToString() == dt1.Rows[j][0].ToString())
{

}
else
{
DataRow dr = dt3.NewRow();
dr["Name"] = dt2.Rows[i][0].ToString();
dt3.Rows.Add(dr);
}
}
}




you will get all your data in datatable dt3

此示例标识值两个表中缺少的值而不是一个表中缺少的值。要仅选择Table_1中缺少的项目，请将完整更改为左侧。



查找不匹配

This example identifies values that are missing from either table instead of values that are missing from just one table. To select only items that are missing from Table_1, change the word Full to Left.

Find mismatched

With CTE (C1,C2) As
 (
 SELECT T1.Col1 As C1,T2.Col1 As C2 FROM Table_2 T2  full join Table_1 T1 On T2.Col1=T1.Col1
 ) 
 Select ISNULL(C1,C2) As Col1 from CTE where C1 Is Null Or C2 Is Null Order By ISNULL(C1,C2)

结果

Results

Col1
E
F

< br $> b $ b

创建测试数据

Create test data

CREATE TABLE [dbo].[Table_1](
    [col1] [nchar](10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
    [col1] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into table_1 values ('A');
insert into table_1 values ('B');
insert into table_1 values ('C');
insert into table_1 values ('D');
insert into table_1 values ('F');

insert into table_2 values ('A');
insert into table_2 values ('B');
insert into table_2 values ('C');
insert into table_2 values ('D');
insert into table_2 values ('E');

这篇关于如何从2个数据表中仅获取不匹配的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！