with cte as
(
    select id=row_number()over(partition by t1.order_id order by t3.calltime),
        t1.order_id, t1.add_time, diff=datediff(second, t1.add_time, t3.calltime)
    from t1
        join t2 on t1.tourist_id=t2.tourist_id
        join t3 on t2.tel1=t3.tel or t2.tel2=t3.tel or t2.tel3=t3.tel or t2.tel4=t3.tel
)
select order_id,
    [1] = max(case id when 1 then diff end),
    [2] = max(case id when 2 then diff end),
    [3] = max(case id when 3 then diff end)
from cte group by order_id

1     2     3
diff1 null  null
null  diff2 null
null  null  diff3

------解决方案--------------------
客户信息表t2: tourist_id(客户信息) tel1 tel2 tel3 tel4(四个电话或手机号码)
-----------
速度瓶颈应该在这里，如果有一个这样的表：
t2_tel：
SQL code
id  tourist_id tel
1   1          tel1
2   1          tel2
3   1          tel3
4   1          tel4
5   2          tel1
6   2          tel2
7   2          tel3
8   3          tel1
。。。

------解决方案--------------------
with r as (
select a.order_id
,a.add_time
,c.calltime
from t1 a inner join
t2 b on a.tourist_id=b.tourist_id cross apply
(select top 3 calltime from t3 where tel=b.tel1 and calltime>a.add_time/*锁定add_time后的电话时间*/ order by calltime) c
union all
select a.order_id
,a.add_time
,c.calltime
from t1 a inner join
t2 b on a.tourist_id=b.tourist_id cross apply
(select top 3 calltime from t3 where tel=b.tel2 and calltime>a.add_time/*锁定add_time后的电话时间*/ order by calltime) c
union all
select a.order_id
,a.add_time
,c.calltime
from t1 a inner join
t2 b on a.tourist_id=b.tourist_id cross apply
(select top 3 calltime from t3 where tel=b.tel3 and calltime>a.add_time/*锁定add_time后的电话时间*/ order by calltime) c