hiveSQL面试题9__不使用distinct或group by去重

0- 描述

表名：t9
表字段及内容：

a     b     c    d
2014  2016  2014   A
2014  2015  2015   B

1- 问题一

描述：不使用distinct或group by去重
输出结果如下所示：

2014  A
2016  A
2014  B
2015  B

参考答案：

select
  t2.year
  ,t2.num
from
 (
  select
    *
    ,row_number() over (partition by t1.year,t1.num) as rank_1
  from 
  (
    select 
      a as year,
      d as num
    from t9
    union all
    select 
      b as year,
      d as num
    from t9
    union all
    select 
      c as year,
      d as num
    from t9
   )t1
)t2
where rank_1=1
order by num;

总结：面试时候讲去重不再只有distinct、group by了，还有row_number() over() 也可以！！！