lateral view的使用（炸开函数）：

 

使用示例：

 

 select ChargeName, regexp_replace(regexp_replace(sub, '\\{', ''), '\\}', '') managebranch,StartDate from (select * from ods_aimsen_base_regionhistories lateral view explode(split(ManageBranchNos,'\\}\\{')) tmp as sub) tmp_a
   

炸开后效果：

 

示例1：

row_number() 函数打行号：

select id,age,name,sex
from 
(select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rank
from t_rownumber) tmp
where rank<=2; 

 

示例2：

 SELECT
    *
FROM
    (
        SELECT
            workno,
            employeename,
            brand,
            null,
            null,
            amount,
            effectivedate,
            isleave,
            leavedate,
            addtime,
            managebranchnos ,
            chargename,
            startdate,
            row_number() over(partition BY workno,employeename,brand,office,team,amount,effectivedate,isleave,leavedate,addtime,managebranchnos ORDER BY startdate DESC) AS rank
        FROM
            (
                SELECT
                    *
                FROM
                    dws.dws_resumeachieve_t_result_tmp02 tmp02
                LEFT JOIN
                    dws.dws_resumeachieve_t_regionhistories_tmp01 tmp_a
                ON
                    tmp02.managebranchnos=tmp_a.managebranch
                WHERE
                tmp02.addtime is not null and tmp02.addtime!='null' and tmp02.managebranchnos is not null  and  tmp02.managebranchnos !='null' and tmp02.leavedate is not null and tmp02.leavedate!='null' and
                --tmp02.addtime is not null and
                    tmp02.addtime>tmp_a.startdate
                    ) a ) tmp

你可以加上筛选条件：
WHERE
    rank is null or rank=1;

效果：