如何从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        |[email protected]   |
| 222  | 1        | 2        |[email protected]  |
| 421  | 2        | 1        |[email protected] |
| 451  | 2        | 2        |[email protected]|
| 578  | 4        | 1        |[email protected]|
| 899  | 4        | 2        |[email protected]  |
+------+----------+----------+---------------+

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

+-----+-------+-------------+----------+----------+----------+----------------+
| ID  | Name  | Birtday_date| CELL_TYPE|  NUMBER  |MAIL_TYPE|Email            |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1   | Byron | 12/10/1998  | 3        | 099141021|1         |[email protected]    |   
| 1   | Byron | 12/10/1998  | 3        | 099141021|2         |[email protected]   |  
| 1   | Byron | 12/10/1998  | 2        | 099091925|1         |[email protected]    | 
| 1   | Byron | 12/10/1998  | 2        | 099091925|2         |[email protected]   | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|1         |[email protected]    | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|2         |[email protected]   |
| 2   | Peter | 01/11/1973  | 1        | 088043153|1         |[email protected]  |
| 2   | Peter | 01/11/1973  | 1        | 088043153|2         |[email protected] |
| 2   | Peter | 01/11/1973  | 2        | 090044313|1         |[email protected]  |
| 2   | Peter | 01/11/1973  | 2        | 090044313|2         |[email protected] |
| 4   | Jose  | 05/02/2008  | 1        | 088043153|1         |[email protected] |
| 4   | Jose  | 05/02/2008  | 1        | 088043153|2         |[email protected]   |
| 4   | Jose  | 05/02/2008  | 2        | 088043153|1         |[email protected] |
| 4   | Jose  | 05/02/2008  | 2        | 088043153|2         |[email protected]   |
+-----+-------+-------------+----------+----------+----------+----------------+

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

+-----+-------+-------------+----------+----------+----------+----------------+
| ID  | Name  | Birtday_date| CELL_TYPE|  NUMBER  |MAIL_TYPE|Email            |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1   | Byron | 12/10/1998  | 3        | 099141021|1         |[email protected]    |   
| 1   | Byron | 12/10/1998  |          |          |2         |[email protected]   |  
| 1   | Byron | 12/10/1998  | 2        | 099091925|          |                | 
| 1   | Byron | 12/10/1998  | 1        | 098041013|          |                | 
| 2   | Peter | 01/11/1973  | 1        | 088043153|1         |[email protected]  |
| 2   | Peter | 01/11/1973  |          |          |2         |[email protected] |
| 2   | Peter | 01/11/1973  | 2        | 090044313|          |                |
| 4   | Jose  | 05/02/2008  | 1        | 092049013|1         |[email protected] |
| 4   | Jose  | 05/02/2008  |          |          |2         |[email protected]   |
| 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"

但是结果出乎我的意料，它们重复了一些行

阿杰·古普塔（Ajay Gupta）

希望这可以帮助。

Create table person(ID int ,Name varchar(20), Birtday_date date)
Insert into person values
(1,'Byron' ,'12/10/1998'),
(2,'Peter' ,'01/11/1973'),
(4,'Jose ' ,'05/02/2008')


Create table phones (ID int,ID_Person int,CELL_TYPE int,NUMBER float)
Insert into phones values
(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)


Create table emails(ID int,ID_Person int, MAIL_TYPE int, Email varchar(100))
Insert into emails values
(221, 1 , 1, '[email protected]   '),
(222, 1 , 2, '[email protected]  '),
(421, 2 , 1, '[email protected] '),
(451, 2 , 2, '[email protected]'),
(578, 4 , 1, '[email protected]'),
(899, 4 , 2, '[email protected]  ')

select p.id, p.name, p.Birtday_date,
       case when Lag(number) over(partition by p.id order by p.id,pe.id) = number then null else cell_type end as cell_type,
       case when Lag(number) over(partition by p.id order by p.id,pe.id) = number then null else number end as number,
       mail_type as mail_type, email as email
from person p left join
     (select pp.ID_Person, cell_type, number, mail_type, email,pp.id from
     (select ID_Person, cell_type, number,id,
              row_number() over (partition by ID_Person order by id ) as seqnum
       from phones 
      ) pp left join
      (select ID_Person,
              mail_type, email, 1 as seqnum
       from emails 
      )e on pp.ID_Person = e.ID_Person and pp.seqnum = e.seqnum
     ) pe
on pe.ID_Person = p.Id
order by  p.id, pe.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。