with a as (select id,t1,rank()over(partition by id order by t1) rank1,count(t1)over(partition
           by id) count1 from dbo.t1),
     b as (select id,t2,rank()over(partition by id order by t2) rank2,count(t2)over(partition
           by id) count2 from dbo.t2)
select a.id,t1,t2,rank1,count1,rank2,count2
from a left join b
on a.id=b.id
where rank1=rank2 or (count1>count2 and rank1>count2 and rank2=count2) or (count1<count2 and rank2>count1 and rank1=count1)
order by a.id,rank1,rank2
结果如下
id    t1    t2    rank1    count1    rank2    count2
1    3    6    1    1    1    1
2    3    3    1    2    1    4
2    4    4    2    2    2    4
2    4    5    2    2    3    4
2    4    7    2    2    4    4
3    2    2    1    3    1    1
3    5    2    2    3    1    1
3    6    2    3    3    1    1
4    3    1    1    2    1    1
4    4    1    2    2    1    1
--
修改:textilerolle FROM 110.182.141.*
FROM 110.182.141.*