跨行的SQL DISTINCT值

Question

我在列中有重复的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

Answer 1

尝试在您的查询中进行以下小更改：

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 x.*,
    row_number() over(partition by x.agreementnumber order by x.telephone) rn
  from (
    select distinct agreementnumber, telephone
    from alternate_mobile 
  ) x
) src
group by agreementnumber;

如果您收到重复的电话，则是因为您在alternate_mobile表中复制了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 x.*,
    row_number() over(partition by x.agreementnumber order by x.telephone) rn
  from (
    select distinct agreementnumber, regexp_replace(telephone,'[^0-9]', '') as telephone
    from alternate_mobile 
  ) x
) src
group by agreementnumber;

Answer 2

注意，您可以通过使用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;