comment find des différences dans la séquence entre deux tables?

Si nous avons ajouté un ID à chaque table et que nous nous sums appuyés sur insert pour signifier quelque chose (ou s'il existe un autre ordinal ou sort à utiliser), vous pouvez faire ceci:

mis à jour pour vérifier en fonction des dossiers avec row_number ():

Exemple de configuration de test: http://rextester.com/VXZIP45848

 create table a ( id int not null identity(1,1) , one varchar(32) , two varchar(32) , three varchar(32)) insert into a values ('folder1','detail1','detail10') ,('folder1','detail2','detail20') ,('folder1','detail3','detail30') ,('folder1','detail 98','detail 198') ,('folder2','detail1','detail10') ,('folder2','detail2','detail20') ,('folder2','detail3','detail30') ,('folder1','detail99','detail 199') ,('folder3','detail1','detail10') ,('folder3','detail2','detail20') ,('folder3','detail3','detail30') ,('folder4','detail1','detail10') ,('folder4','detail2','detail20') ,('folder4','detail3','detail30'); create table b ( id int not null identity(1,1) , one varchar(32) , two varchar(32) , three varchar(32)) insert into b values ('folder1','detail3','detail30') ,('folder1','detail2','detail20') ,('folder1','detail1','detail10') ,('folder2','detail1','detail10') ,('folder2','detail2','detail20') ,('folder2','detail3','detail30') ,('folder3','detail1','detail10') ,('folder3','detail3','detail30') ,('folder3','detail2','detail20') ,('folder4','detail1','detail10') ,('folder4','detail3','detail30') ,('folder5','detail5','detail50'); /* show all a and all b when matched and when not */ select a.*, b.*, diff=case when a.rn=b.rn then '' when a.id is null then 'not in a' when b.id is null then 'not in b' when a.rn!=b.rn then 'switched' else '?' end from (select * ,rn = row_number() over (partition by one order by id) from a) as a full outer join (select * ,rn = row_number() over (partition by one order by id) from b) as b on a.one=b.one and a.two=b.two and a.three=b.three 

résulte en:

 +------+---------+-----------+------------+------+------+---------+---------+----------+------+----------+ | id | one | two | three | rn | id | one | two | three | rn | diff | +------+---------+-----------+------------+------+------+---------+---------+----------+------+----------+ | 1 | folder1 | detail1 | detail10 | 1 | 3 | folder1 | detail1 | detail10 | 3 | switched | | 2 | folder1 | detail2 | detail20 | 2 | 2 | folder1 | detail2 | detail20 | 2 | | | 3 | folder1 | detail3 | detail30 | 3 | 1 | folder1 | detail3 | detail30 | 1 | switched | | 4 | folder1 | detail 98 | detail 198 | 4 | NULL | NULL | NULL | NULL | NULL | not in b | | 8 | folder1 | detail99 | detail 199 | 5 | NULL | NULL | NULL | NULL | NULL | not in b | | 5 | folder2 | detail1 | detail10 | 1 | 4 | folder2 | detail1 | detail10 | 1 | | | 6 | folder2 | detail2 | detail20 | 2 | 5 | folder2 | detail2 | detail20 | 2 | | | 7 | folder2 | detail3 | detail30 | 3 | 6 | folder2 | detail3 | detail30 | 3 | | | 9 | folder3 | detail1 | detail10 | 1 | 7 | folder3 | detail1 | detail10 | 1 | | | 10 | folder3 | detail2 | detail20 | 2 | 9 | folder3 | detail2 | detail20 | 3 | switched | | 11 | folder3 | detail3 | detail30 | 3 | 8 | folder3 | detail3 | detail30 | 2 | switched | | 12 | folder4 | detail1 | detail10 | 1 | 10 | folder4 | detail1 | detail10 | 1 | | | 13 | folder4 | detail2 | detail20 | 2 | NULL | NULL | NULL | NULL | NULL | not in b | | 14 | folder4 | detail3 | detail30 | 3 | 11 | folder4 | detail3 | detail30 | 2 | switched | | NULL | NULL | NULL | NULL | NULL | 12 | folder5 | detail5 | detail50 | 1 | not in a | +------+---------+-----------+------------+------+------+---------+---------+----------+------+----------+ 

Comme il n'y a pas d'ordinal ou de sorting, find des cas de 'switching' n'est pas possible dans ce cas.

Exemple de configuration de test: http://rextester.com/YEIAN6814

 /* since there is no ordinal or sort, finding cases of 'switching' aren't possible in this case. */ create table a (one varchar(32), two varchar(32), three varchar(32)) insert into a values ('folder1','detail1','detail10') ,('folder1','detail2','detail20') ,('folder1','detail3','detail30') ,('folder2','detail1','detail10') ,('folder2','detail2','detail20') ,('folder2','detail3','detail30') ,('folder3','detail1','detail10') ,('folder3','detail2','detail20') ,('folder3','detail3','detail30') ,('folder4','detail1','detail10') ,('folder4','detail2','detail20') ,('folder4','detail3','detail30'); create table b (one varchar(32), two varchar(32), three varchar(32)) insert into b values ('folder1','detail3','detail30') ,('folder1','detail2','detail20') ,('folder1','detail1','detail10') ,('folder2','detail1','detail10') ,('folder2','detail2','detail20') ,('folder2','detail3','detail30') ,('folder3','detail1','detail10') ,('folder3','detail3','detail30') ,('folder3','detail2','detail20') ,('folder4','detail1','detail10') ,('folder4','detail3','detail30') ,('folder5','detail5','detail50'); /* added this value for example*/ 

Full Outer Join: montrez tout a et tout b soit assorti ou non

 /* show all a and all b when matched and when not */ select a.*, b.* , diff=case when a.one is null then 'not in a' when b.one is null then 'not in b' else '' end from a full outer join b on a.one =b.one and isnull(a.two,'') =isnull(b.two,'') and isnull(a.three,'')=isnull(b.three,'') --and (a.two =b.two or (a.two is null and b.two is null)) --and (a.three=b.three or (a.three is null and b.three is null)) +---------+---------+----------+---------+---------+----------+----------+ | one | two | three | one | two | three | diff | +---------+---------+----------+---------+---------+----------+----------+ | folder1 | detail1 | detail10 | folder1 | detail1 | detail10 | | | folder1 | detail2 | detail20 | folder1 | detail2 | detail20 | | | folder1 | detail3 | detail30 | folder1 | detail3 | detail30 | | | folder2 | detail1 | detail10 | folder2 | detail1 | detail10 | | | folder2 | detail2 | detail20 | folder2 | detail2 | detail20 | | | folder2 | detail3 | detail30 | folder2 | detail3 | detail30 | | | folder3 | detail1 | detail10 | folder3 | detail1 | detail10 | | | folder3 | detail2 | detail20 | folder3 | detail2 | detail20 | | | folder3 | detail3 | detail30 | folder3 | detail3 | detail30 | | | folder4 | detail1 | detail10 | folder4 | detail1 | detail10 | | | folder4 | detail2 | detail20 | NULL | NULL | NULL | not in b | | folder4 | detail3 | detail30 | folder4 | detail3 | detail30 | | | NULL | NULL | NULL | folder5 | detail5 | detail50 | not in a | +---------+---------+----------+---------+---------+----------+----------+ 

Joindre à gauche: tout a , b cas d'appariement

 /* show all a, b matches */ select a.*, b.* from a left join b on a.one =b.one and isnull(a.two,'') =isnull(b.two,'') and isnull(a.three,'')=isnull(b.three,'') --and (a.two =b.two or (a.two is null and b.two is null)) --and (a.three=b.three or (a.three is null and b.three is null)) +---------+---------+----------+---------+---------+----------+ | one | two | three | one | two | three | +---------+---------+----------+---------+---------+----------+ | folder1 | detail1 | detail10 | folder1 | detail1 | detail10 | | folder1 | detail2 | detail20 | folder1 | detail2 | detail20 | | folder1 | detail3 | detail30 | folder1 | detail3 | detail30 | | folder2 | detail1 | detail10 | folder2 | detail1 | detail10 | | folder2 | detail2 | detail20 | folder2 | detail2 | detail20 | | folder2 | detail3 | detail30 | folder2 | detail3 | detail30 | | folder3 | detail1 | detail10 | folder3 | detail1 | detail10 | | folder3 | detail2 | detail20 | folder3 | detail2 | detail20 | | folder3 | detail3 | detail30 | folder3 | detail3 | detail30 | | folder4 | detail1 | detail10 | folder4 | detail1 | detail10 | | folder4 | detail2 | detail20 | NULL | NULL | NULL | | folder4 | detail3 | detail30 | folder4 | detail3 | detail30 | +---------+---------+----------+---------+---------+----------+ 

Not Exists (): toutes les lignes d' a qui ne sont pas dans b

 /* all in a that aren't in b */ select src='a not in b', * from a where not exists ( select 1 from b where a.one=b.one and isnull(a.two,'') =isnull(b.two,'') and isnull(a.three,'')=isnull(b.three,'') ); +------------+---------+---------+----------+ | src | one | two | three | +------------+---------+---------+----------+ | a not in b | folder4 | detail2 | detail20 | +------------+---------+---------+----------+ 

Not Exists (): toutes les lignes de b qui ne sont pas dans a

 /* all in b that aren't in a */ select src='b not in a', * from b where not exists ( select 1 from a where a.one=b.one and isnull(a.two,'') =isnull(b.two,'') and isnull(a.three,'')=isnull(b.three,'') ); +------------+---------+---------+----------+ | src | one | two | three | +------------+---------+---------+----------+ | b not in a | folder5 | detail5 | detail50 | +------------+---------+---------+----------+ 

Sauf: valeurs distinctes dans a qui ne sont pas dans b

 /* distinct values in a that aren't in b */ select * from a except select * from b; +---------+---------+----------+ | one | two | three | +---------+---------+----------+ | folder4 | detail2 | detail20 | +---------+---------+----------+ 

Intersection: valeurs distinctes qui sont dans a qui sont également dans b

 /* distinct values that are in a that are also in b */ select * from a intersect select * from b; +---------+---------+----------+ | one | two | three | +---------+---------+----------+ | folder1 | detail1 | detail10 | | folder1 | detail2 | detail20 | | folder1 | detail3 | detail30 | | folder2 | detail1 | detail10 | | folder2 | detail2 | detail20 | | folder2 | detail3 | detail30 | | folder3 | detail1 | detail10 | | folder3 | detail2 | detail20 | | folder3 | detail3 | detail30 | | folder4 | detail1 | detail10 | | folder4 | detail3 | detail30 | +---------+---------+----------+