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() 也可以!!!