55表结构

建表

create table t9
(
    a string,
    b string,
    c string,
    d string
);
insert into t9
values ('2014', '2016', '2014', 'A'),
       ('2014', '2015', '2015', 'B');

1）不使用distinct和group by分组

输出结果

思路

-- 1,先将多个显示年份的列转为1列，用_union all_ -- 2,使用分组排序的开窗函数(row_number())对两个字段分别进行分组，这样排序就会在a分组内，b再分组，取b的排序 -- 3,取排序为1的值即可

SQL

select year,
num
from (select year,
      num,
      row_number() over (partition by tmp1.year,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) tmp1) tmp2
where tmp2.rank_1 = 1;

#