sql - 列出一个人及其第 n 个电话号码

Question

我正在使用 PowerSchool (Oracle DB)，为了导出我们的紧急机器人呼叫服务，我需要提取学生报告以及与他们相关的前 7 个紧急电话号码。我有一个查询，它只提取紧急号码，但无法将第 n 个电话记录拉到正确的上下文中。当我运行当前代码时，我得到“ORA-00923：FROM 关键字未在预期的地方找到”。

我可以从 WITH 语句中的查询中选择所有值并按预期​​运行。但是，我不知道如何在主查询中正确获取它们。

WITH emerglist as (
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 1 as phone_order, emergnumbers.contact_phone_1 as phone_number
    from emergnumbers where contact_phone_1 is not null
    UNION
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 2 as phone_order, emergnumbers.contact_phone_2 as phone_number
    from emergnumbers where contact_phone_2 is not null
    UNION
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 3 as phone_order, emergnumbers.contact_phone_3 as phone_number
    from emergnumbers where contact_phone_3 is not null
)
--select * from emerglist;  -- this works

select distinct 
    s.student_number as referencecode,
    s.first_name as firstname,
    s.last_name as lastname,
    --emergency numbers
    (select enum.phone_number from enum where rn=1) as emerg_1
    (select enum.phone_number from enum where rn=2) as emerg_2,
    (select enum.phone_number from enum where rn=3) as emerg_3,
    (select enum.phone_number from enum where rn=4) as emerg_4,
    (select enum.phone_number from enum where rn=5) as emerg_5,
    (select enum.phone_number from enum where rn=6) as emerg_6,
    (select enum.phone_number from enum where rn=7) as emerg_7
from students s
    left outer join (  select emerglist.*, rownum rn from emerglist order by contact_priority_order, phone_order  ) enum on s.dcid = enum.stu_dcid             
where s.enroll_status=0
;

编辑添加：我的目标是每个学生有一行，填写尽可能多的紧急号码列，没有间隙（例如不能有emerg1 = #，emerg_2 = null，emerg_3 = #）。每个学生可以有 0-10 个联系人，每个联系人可以有 0-5 个号码。我们限制为每位学生 7 个号码。这是一个更大查询的一个子集，它还为普通消息（如下雪天、电子邮件地址和其他人口统计数据）提取主要数字。除了紧急号码外，所有这些都有效。这些只有在真正的紧急情况下才会使用，并且我们希望达到尽可能多的数字。

来自 EmergList 的示例数据：

STU_DCID   CONTACT_PRIORITY_ORDER   PHONE_ORDER   PHONE_NUMBER
52  1   1   [phone]
52  2   1   [phone]
52  2   2   [phone]
58  1   1   [phone]
58  1   2   [phone]

期望的结果：

REFERENCECODE   FIRSTNAME   LASTNAME   EMERG_1   EMERG_2   EMERG_3   EMERG_4   EMERG_5   EMERG_6   EMERG_7
52   John   Smith   [phone]   [phone]   [phone]
58   Mary   Jones   [phone]   [phone]

标签： sqloraclesubquery

Answer 1

你如何做到这一点的方法之一是下一个......

select STU_DCID, 
   MAX("1") "1", 
   MAX("2") "2",
   MAX("3") "3",
   MAX("4") "4", 
   MAX("5") "5",
   MAX("6") "6",
   MAX("7") "7", 
   MAX("8") "8",
   MAX("9") "9",
   MAX("10") "10"
from (
   select STU_DCID, 
          CONTACT_PRIORITY_ORDER, 
          PHONE_ORDER, 
          PHONE_NUMBER,
          ROW_NUMBER() OVER (PARTITION BY STU_DCID 
                             ORDER BY CONTACT_PRIORITY_ORDER, PHONE_ORDER) RN
   from EmergList) src
PIVOT (
MAX(PHONE_NUMBER)
FOR RN IN (1 "1", 2 "2", 3 "3",
           4 "4", 5 "5", 6 "6",
           7 "7", 8 "8", 9 "9",
           10 "10")
) GROUP BY STU_DCID;

然后你只需更改列的别名并添加额外的连接src以获得学生姓名......你会得到的当前结果是这个

STU_DCID    |       1       |       2        |      3         |4|5|6|7|8|9|10|
    52      |[phone] | [phone] | [phone] | | | | | | | |
    58      |[phone] | [phone] |                | | | | | | | |