Oracle SELECT QUERY用于两个表之间的一对多关系 [英] Oracle SELECT QUERY for one to many relationship between two tables

问题描述

我不是SQL专家，但我正在学习.我试图弄清楚如何以一对多关系编写两个表的查询.

I am not a SQL expert but I am learning. I am trying to figure out how to write the query for two table in a one to many relationship.

Table1具有人员信息: (PersonId, FirstName, MiddleName, LastName)和table2具有(PersonId, Phone, PhoneType).

Table1 has the person information : (PersonId, FirstName, MiddleName, LastName) and table2 has (PersonId, Phone, PhoneType).

这是我到目前为止的查询

Here is my query so far

select Table1.PERSON_ID, 
Table1.FIRST_NAME, 
Table1.MIDDLE_NAME, 
Table1.LAST_NAME, 
Table2.PHONE_NUMBER
from Table1
inner join Table2
on Table2.PERSON_ID = Table1.PERSON_ID
where Table2.PHONE_TYPE in ('BUSINESS','PERSONAL','HOME')

这是一张桌子

Table1
PERSON_ID     FIRST_NAME     MIDDLE_NAME     LAST_NAME
1             John           Carter          Jones

Table2
PERSON_ID     PHONE_NUMBER   PHONE_TYPE
1             111-111-1111   HOME
1             111-111-1112   PERSONAL
1             111-111-1113   BUSINESS

从查询中我得到

1     John Carter Jones 111-111-1111
1     John Carter Jones 111-111-1112
1     John Carter Jones 111-111-1113

我想获得以下结果

1     John Carter Jones 111-111-1111 111-111-1112 111-111-1113

我尝试使用LISTAGG组合电话号码.它在同一列中给了我所有3个电话号码.如何将LISTAGG的输出分为3个单独的列.

I tried using LISTAGG to combine the phone numbers. It gives me all 3 phone numbers in one column. How can I get the output of the LISTAGG as 3 separate columns.

TOAD上的输出如下所示

The output on TOAD looks like this

1 John M. Doe"2022222222，2023333333"

1 John M. Doe "2022222222, 2023333333"

这是我的查询

select 
PERSON.PERSON_ID, 
PERSON.FIRST_NAME,
PERSON.MIDDLE_NAME,
PERSON.LAST_NAME,
LISTAGG(PHONE_NUMBER, ',') WITHIN GROUP (ORDER BY PHONENUMBERS.PHONE_NUMBER) 
FROM Table1 PERSON
INNER JOIN (SELECT PERSON_ID, PHONE_NUMBER, UPDATED_DT, PHONE_TP_SHORT_DESC FROM Table2) PHONENUMBERS
ON PERSON.PERSON_ID = PHONENUMBERS.PERSON_ID AND PHONENUMBERS.PHONE_TP_SHORT_DESC IN ('HOME','BUSINESS','CELL')
GROUP BY   
PERSON.PERSON_ID, 
PERSON.FIRST_NAME,
PERSON.MIDDLE_NAME,
PERSON.LAST_NAME

推荐答案

一种方法使用条件聚合:

One method uses conditional aggregation:

select t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME, 
       max(case when t2.phone_type = 'BUSINESS' then t2.PHONE_NUMBER end) as business,
       max(case when t2.phone_type = 'PERSONAL' then t2.PHONE_NUMBER end) as personal,
       max(case when t2.phone_type = 'HOME' then t2.PHONE_NUMBER end) as home
from Table1 t1 inner join
     Table2 t2
     on t2.PERSON_ID = t1.PERSON_ID
where t2.PHONE_TYPE in ('BUSINESS', 'PERSONAL', 'HOME') 
group by t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME;

这篇关于Oracle SELECT QUERY用于两个表之间的一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！