select id
,(case city
when 'gz' then '020'
when 'sz' then '0755'
when 'zh' then '0756'
when 'st' then '0754'
when 'sg' then '0751'
when 'mz' then '0753'
when 'hz' then '0752'
when 'sw' then '0660'
when 'dg' then '0769'
when 'zs' then '0760'
when 'jm' then '0750'
when 'fs' then '0757'
when 'yj' then '0662'
when 'zj' then '0759'
when 'mm' then '0668'
when 'zq' then '0758'
when 'qy' then '0763'
when 'cz' then '0768'
when 'jy' then '0663'
when 'yf' then '0766'
when 'hy' then '0762'
end) as 区号
,(case level1 when 1 then '钻卡' when 2 then '金卡' when 3 then '银卡' end) as 等级
,(case type when 1 then '政企客户' when 2 then '家庭客户' when 3 then '个人客户' end) as 客户类型
,num
from (select row_number() over(partition by city,level1,type order by num desc) row_,
t.* from table1 t)
where row_=1
,(case city
when 'gz' then '020'
when 'sz' then '0755'
when 'zh' then '0756'
when 'st' then '0754'
when 'sg' then '0751'
when 'mz' then '0753'
when 'hz' then '0752'
when 'sw' then '0660'
when 'dg' then '0769'
when 'zs' then '0760'
when 'jm' then '0750'
when 'fs' then '0757'
when 'yj' then '0662'
when 'zj' then '0759'
when 'mm' then '0668'
when 'zq' then '0758'
when 'qy' then '0763'
when 'cz' then '0768'
when 'jy' then '0663'
when 'yf' then '0766'
when 'hy' then '0762'
end) as 区号
,(case level1 when 1 then '钻卡' when 2 then '金卡' when 3 then '银卡' end) as 等级
,(case type when 1 then '政企客户' when 2 then '家庭客户' when 3 then '个人客户' end) as 客户类型
,num
from (select row_number() over(partition by city,level1,type order by num desc) row_,
t.* from table1 t)
where row_=1