求sql

id    tid   isok
1     1     合格
2     1     合格
3     1     合格  
4     2     不合格 

id    tid   isok
1     1     合格
2     1     合格
3     1     合格 
4     2     合格 

isok  tid
合格   1
不合格  2

with cte as
(
    select distinct tid from t1
    union
    select distinct tid from t2
)

select distinct a.tid,(case when b.tid is null and c.tid is null then '合格' else '不合格' end) isok
from cte a left join t1 b on a.tid = b.tid and b.isok = N'不合格'
           left join t2 c on a.tid = c.tid and c.isok = N'不合格'

with cte as
(
  select row_number() over(partition by tid order by len(isok) desc) no,*
   from (select tid,isok from table1 union select tid,isok from table2) a
)

select tid,isok from cte where no=1