(SELECT
mt4_account, mt4_account AS team_leader
FROM
table_x
WHERE
mt4_account_type = 5
)
union all
(select mt4_account, referee as team_leader
from
(SELECT
mt4_account, (LOCATE('5', REVERSE(identity_relation))-1)/2 as n,total_relation,mt4_account_type,referee
FROM
table_x) as a
where mt4_account_type !=5
and n = 1
)
union all
(select mt4_account, mid(total_relation,(n-1)*9+1,8) as team_leader
from
(SELECT
mt4_account, (LOCATE('5', REVERSE(identity_relation))-1)/2 as n,total_relation,mt4_account_type
FROM
table_x) as a
where mt4_account_type !=5
and n >1
)
