sql—在配置单元中查找具有相同最后两位雇员id的雇员

6ie5vjzr  于 2021-05-29  发布在  Hadoop
关注(0)|答案(1)|浏览(286)

我在Hive里有一个下面的场景

Emp ID | Employee name | Emp salary    
112    | Prakash       | 33333       
212    | Aakash        | 5567
322    | Kishore       | 565
3242   | hhjh          | 76676
4325   | chhh          | 565
422    | Ramesh        | 34555

我想找出员工id最后两位数字相同的员工姓名:
例如,prakash和aakash有相同的雇员id的最后一位,kishore和ramesh也有相同的数字。
输出:

112    | Prakash            
212    | Aakash       
322    | Kishore  
422    | Ramesh
7xllpg7q

7xllpg7q1#

select  `Emp ID`
       ,`Employee name`

from   (select  `Emp ID`
               ,`Employee name`
               ,count(*) over (partition by `Emp ID` % 100) as cnt

        from    mytable
        ) t

where   cnt > 1
;
+--------+---------------+
| emp id | employee name |
+--------+---------------+
|    212 | Aakash        |
|    112 | Prakash       |
|    422 | Ramesh        |
|    322 | Kishore       |
+--------+---------------+

相关问题