时间戳

首页 > 解决方案 > 如何从 oracle join 中删除重复值？

sql - 如何从 oracle join 中删除重复值？

问题描述

我想创建一个只显示结果而不显示重复项的视图，我在 oracle 数据库中有 3 个表：

第一个表包含一个人的一般信息

+-----------+-------+-------------+
| ID        | Name  | Birtday_date|
+-----------+-------+-------------+
| 1         | Byron | 12/10/1998  |
| 2         | Peter | 01/11/1973  |
| 4         | Jose  | 05/02/2008  |
+-----------+-------+-------------+

第二个表包含有关第一个表中人员的电话的信息。

+-------+----------+----------+----------+
| ID    |ID_Person |CELL_TYPE | NUMBER   |
+-------+- --------+----------+----------+
| 1221  | 1        | 3        | 099141021|
| 2221  | 1        | 2        | 099091925|
| 3222  | 1        | 1        | 098041013|
| 4321  | 2        | 1        | 088043153|
| 4561  | 2        | 2        | 090044313|
| 5678  | 4        | 1        | 092049013|
| 8990  | 4        | 2        | 098090233|
+----- -+----------+----------+----------+

第三个表包含有关第一个表中人员的电子邮件的信息。

+------+----------+----------+---------------+
| ID   |ID_Person |MAIL_TYPE | Email         |
+------+- --------+----------+---------------+
| 221  | 1        | 1        |jdoe@aol.com   |
| 222  | 1        | 2        |jdoe1@aol.com  |
| 421  | 2        | 1        |xx12@yahoo.com |
| 451  | 2        | 2        |dsdsa@gmail.com|
| 578  | 4        | 1        |sasaw1@sdas.com|
| 899  | 4        | 2        |cvcvsd@wew.es  |
+------+----------+----------+---------------+

如果我对此表进行内部联接，结果将执行类似的操作

+-----+-------+-------------+----------+----------+----------+----------------+
| ID  | Name  | Birtday_date| CELL_TYPE|  NUMBER  |MAIL_TYPE|Email            |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1   | Byron | 12/10/1998  | 3        | 099141021|1         |jdoe@aol.com    |   
| 1   | Byron | 12/10/1998  | 3        | 099141021|2         |jdoe1@aol.com   |  
| 1   | Byron | 12/10/1998  | 2        | 099091925|1         |jdoe@aol.com    | 
| 1   | Byron | 12/10/1998  | 2        | 099091925|2         |jdoe1@aol.com   | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|1         |jdoe@aol.com    | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|2         |jdoe1@aol.com   |
| 2   | Peter | 01/11/1973  | 1        | 088043153|1         |xx12@yahoo.com  |
| 2   | Peter | 01/11/1973  | 1        | 088043153|2         |dsdsa@gmail.com |
| 2   | Peter | 01/11/1973  | 2        | 090044313|1         |xx12@yahoo.com  |
| 2   | Peter | 01/11/1973  | 2        | 090044313|2         |dsdsa@gmail.com |
| 4   | Jose  | 05/02/2008  | 1        | 088043153|1         |sasaw1@sdas.com |
| 4   | Jose  | 05/02/2008  | 1        | 088043153|2         |cvcvsd@wew.es   |
| 4   | Jose  | 05/02/2008  | 2        | 088043153|1         |sasaw1@sdas.com |
| 4   | Jose  | 05/02/2008  | 2        | 088043153|2         |cvcvsd@wew.es   |
+-----+-------+-------------+----------+----------+----------+----------------+

所以我将在视图中呈现的结果是下一个

+-----+-------+-------------+----------+----------+----------+----------------+
| ID  | Name  | Birtday_date| CELL_TYPE|  NUMBER  |MAIL_TYPE|Email            |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1   | Byron | 12/10/1998  | 3        | 099141021|1         |jdoe@aol.com    |   
| 1   | Byron | 12/10/1998  |          |          |2         |jdoe1@aol.com   |  
| 1   | Byron | 12/10/1998  | 2        | 099091925|          |                | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|          |                | 
| 2   | Peter | 01/11/1973  | 1        | 088043153|1         |xx12@yahoo.com  |
| 2   | Peter | 01/11/1973  |          |          |2         |dsdsa@gmail.com |
| 2   | Peter | 01/11/1973  | 2        | 090044313|          |                |
| 4   | Jose  | 05/02/2008  | 1        | 092049013|1         |sasaw1@sdas.com |
| 4   | Jose  | 05/02/2008  |          |          |2         |cvcvsd@wew.es   |
| 4   | Jose  | 05/02/2008  | 2        | 098090233|          |                |
+-----+-------+-------------+----------+----------+----------+----------------+

我尝试使用来实现类似的输出

case
  when row_number() over (partition by table1.id order by table2.type) = 1
  then table1.value
 end
   as "VALUE"

但结果出乎我的意料，有些行他们重复

标签： sqloracleoracle11ginner-join

解决方案

您需要做的是枚举行，然后加入这些枚举。这很棘手，因为您不知道每个列表中有多少。好吧，还有另一种使用条件聚合的方法：

select p.id, p.name, p.birthday,
       max(cell_type) as cell_type, max(number) as number,
       max(mail_type) as mail_type, max(email) as email
from person p left join
     ((select id_person, cell_type, number,
              null as mail_type, null as email,
              row_number() over (partition by id_person order by number) as seqnum
       from phones
      ) union all
      (select id_person, null as cell_type, null as number,
              mail_type, email,
              row_number() over (partition by id_person order by email) as seqnum
       from emails
      )
     ) pe
     on pe.id_person = p.id_person
group by p.id, p.name, p.birthday, pe.seqnum

推荐阅读