时间戳

首页 > 解决方案 > 跨行的 SQL DISTINCT 值

sql - 跨行的 SQL DISTINCT 值

问题描述

我在列中有重复agreementnumber和重复telephone，我想获得唯一性agreementnumber，并且它在列中是对应的唯一telephone性。

我用 SQL 编写了查询，这给了我唯一agreementnumber但telephone行是重复的，但我想要唯一的电话号码。

代码：

select agreementnumber,

  max(case when rn = 1 then telephone end) telephone1,

  max(case when rn = 2 then telephone end) telephone2,

  max(case when rn = 3 then telephone end) telephone3,

  max(case when rn = 4 then telephone end) telephone4,

  max(case when rn = 5 then telephone end) telephone5

from
(
  select agreementnumber, telephone,

    row_number() over(partition by agreementnumber order by telephone) rn
  from alternate_mobile 

) src
group by agreementnumber;

我想要以下输出。col1 和 col2,col3,col4,col4 中的唯一值。

col1 col2 col3 col4`` AGMTNO phone1 phone2 phone3

标签： sql

解决方案

请注意，您可以通过使用rank()而不是减少子查询的数量row_number()：

select agreementnumber,
       max(case when rn = 1 then telephone end) as telephone1,
       max(case when rn = 2 then telephone end) as telephone2,   
       max(case when rn = 3 then telephone end) as telephone3,
       max(case when rn = 4 then telephone end) as telephone4,
       max(case when rn = 5 then telephone end) as telephone5
from (select am.*,
             rank() over (partition by am.agreementnumber order by am.telephone) as rn
      from alternate_mobile am
     ) am
group by agreementnumber;

推荐阅读