需要T sql查询的帮助

I have two tables. The data in those are something like below:

我有两个表。这些数据如下所示:

Table 1:

表1:

Col1      Col2
----------------
A          A1
A          A2
A          A3
B          B1
B          B2
B          B3

Table 2:

表2:

Col1        Col2
------------------
A            A1
A            A4
A            A5
B            B1
B            B4
B            B5

I need to bring out the difference in data in Col2 of two tables based on the values in Col1. The output should look something like below:

我需要根据Col1中的值得出两个表Col2中的数据差异。输出应该如下所示:

Output:

输出:

Col    MismatchValuesInTable1     Mismatchvaluesintable2
---------------------------------------------------------
A               A2                       A4
                A3                       A5
B               B2                       B4
                B3                       B5

Please help me with a query to achieve the above.

请帮助我查询以上内容。

2 个解决方案

#1

select isnull(t1.Col1,t2.Col2) as Col,
  t1.Col2 as MismatchValuesInTable1, 
  t2.Col2 as MismatchValuesInTable2

from t1
FULL JOIN t2 on (t1.Col1=T2.Col1) and (t1.Col2=t2.Col2)
where t1.Col2 is null or t2.Col2 is null
order by Col

SQLFiddle demo

SQLFiddle演示

#2

This will produce the output table you have. Not sure if the position matters in your problem? If so, this matches based on the fact that the second value for A in table T1 doesn't match the second value for A in table T2.

这将生成您拥有的输出表。不确定这个职位是否与你的问题有关?如果是的话，这是基于表T1中的第二个值与表T2中的第二个值不匹配的事实。

create table T1
(col1 varchar(10),
col2 varchar(10))

create table T2
(col1 varchar(10),
col2 varchar(10))


insert into T1
select 'A','A1'
union all
select 'A','A2'
union all
select 'A','A3'
union all
select 'B','B1'
union all
select 'B','B2'
union all
select 'B','B3'

insert into T2
select 'A','A1'
union all
select 'A','A4'
union all
select 'A','A5'
union all
select 'B','B1'
union all
select 'B','B4'
union all
select 'B','B5'

select T1.col1, T1.col2, T2.col2
from (select row_number() over(partition by Col1 order by Col1, Col2) as row_number, 
* from T1) as T1
inner join 
(select row_number() over(partition by Col1 order by Col1, Col2) as row_number, 
* from B) as T2
on T1.col1 = T2.col1
and T1.row_number = T2.row_number
where T1.col2 <> T2.col2