多列去重

province city score
shanxi lvliang 1
shanxi lvliang 2
shanxi lvliang 3
shanxi lvliang 4
shanxi lvliang 5
shanxi yuncheng 6
shanxi yuncheng 7
shanxi yuncheng 8
beijing chaoyang 9
beijing chaoyang 10
beijing chaoyang 11
beijing chaoyang 12
beijing chaoyang 13
beijing chaoyang 14
beijing changping 15
beijing changping 16
beijing changping 17
beijing haidian 18
beijing haidian 19
beijing haidian 20

 

 

（1）--119.554 seconds, Fetched: 5 row(s)

select province,city from kg_fk_city group by province,city;

 

beijing changping
beijing chaoyang
beijing haidian
shanxi lvliang
shanxi yuncheng

 （2）203.915 seconds, Fetched: 5 row(s)

select t.province,t.city from
(
select province,city,ROW_Number() over(partition by province,city order by province) rank
from kg_fk_city
) t
where t.rank=1;
beijing changping
beijing chaoyang
beijing haidian
shanxi lvliang
shanxi yuncheng

（3）2106.453 seconds, Fetched: 5 row(s)

SELECT province,city,collect_set(score)[0] as score FROM kg_fk_city GROUP BY province,city;

beijing changping 15
beijing chaoyang 9
beijing haidian 18
shanxi lvliang 1
shanxi yuncheng 6