Группировка 2-х таблиц

declare @a table (a int, pr int);
declare @b table (b int, pr int);
 
insert into @a
values
 (1, 1), (1, 2), (1, 4),
 (2, 11), (2, 10),
 (3, 5), (3, 6), (3, 14),
 (4, 1), (4, 2), (4, 5);
 
insert into @b
values
 (7, 1), (7, 2), (7, 4),
 (20, 111), (20, 101),
 (30, 50), (30, 60), (30, 14),
 (8, 1), (8, 2), (8, 5);
 
with a as
(
 select a, pr, count(*) over (partition by a) as c, dense_rank() over (partition by a order by pr) as r from @a
),
b as
(
 select b, pr, count(*) over (partition by b) as c, dense_rank() over (partition by b order by pr) as r from @b
)
select
 a.a, b.b
from
 a join
 b on b.pr = a.pr and b.r = a.r and b.c = a.c
group by
 a.a, b.b
having
 count(*) = max(a.c);

select
 a.a, b.b
from
 (select a, count(distinct pr) from @a group by a) t(a, c) join
 @a a on a.a = t.a left join
 @b b on b.pr = a.pr
group by
 a.a, b.b, t.c
having
 count(distinct b.pr) = t.c;

select
 a.a, b.b, count(distinct a.pr), count(distinct b.pr)
from
 @a a left join
 @b b on b.pr = a.pr
group by
 a.a, b.b
having
 count(distinct b.pr) = (select count(distinct pr) from @a where a = a.a);

 
select 
a.a, b.b, count(distinct a.pr), count(distinct b.pr) 
from 
@a a left join 
@b b on b.pr = a.pr 
group by 
a.a, b.b 
having 
count(distinct b.pr) = (select count(distinct pr) from @a where a = a.a);

select
 t1.name, t2.name
from
 @a a join
 Table1 t1 on t1.a = a.a left join
 (
  @b b join
  Table2 t2 on t2.b = b.b
 ) on b.pr = a.pr
group by
 a.a, b.b, t1.name, t2.name
having
 count(distinct b.pr) = (select count(distinct pr) from @a where a = a.a);