可以使用子字符串索引修剪最后两个句点之间的字符
所以你的问题如下
select
case when parent ='' then '-' else concat(parent,'.') end as parent ,
userid,
name
from
(
select
TRIM(TRAILING concat('.',substring_index(TRIM(TRAILING '.' from USERID), '.', -1),'.') from USERID) as parent,
NAME as name,
USERID from user
)tmp
工作小提琴
http://sqlfiddle.com/#!9/36435a/13
