select substring(longOpenId, 1, 28) as id1,
       substring(longOpenId, 29,56) as id2,
       u1.*,
       u2.*
from couple 
  left join user u1 on substring(longOpenId, 1, 28) = u1.open_id
  left join user u2 on substring(longOpenId, 29,56) = u2.open_id;