select A.id,A.name,A.basename,A.num,B.basename,B.num,C.basename,C.num
from test A left join test B on A.id=B.id left join test C on A.id=C.id
Where A.basename!=B.basename and A.basename!=C.basename and C.basename!=B.basename and A.baseid<B.baseid and B.baseid<C.baseid
from test A left join test B on A.id=B.id left join test C on A.id=C.id
Where A.basename!=B.basename and A.basename!=C.basename and C.basename!=B.basename and A.baseid<B.baseid and B.baseid<C.baseid
max(case basename when '广州' then num end),
max(case basename when '上海' then basename end),
max(case basename when '上海' then num end),
max(case basename when '北京' then basename end),
max(case basename when '北京' then num end)
from test
group by id,name