加入 2 个表并按 id、优先级和显示数字 [英] Join 2 tables and display by id,priority & number

问题描述

示例表和所需结果:下面显示的结果表是我真正想要的输出.

Example tables and desired result: The result table shown below is the output I actually want.

使用数据透视尝试了以下查询:

tried the following query with pivot:

with pivot_data AS
(
  select client_id
        ,ph_type
        ,Ph_number
  from client_table
    inner join phone_table
      on client_table.phone_id = phone_table.ph_id
)
select *
from pivot_data
pivot (sum(ph_number)
       for ph_type in ('c','w','h')
      );

我得到的结果:

任何帮助将不胜感激.sql server 中的答案会很棒，但 oracle &如果他们能指出我正确的方向，也欢迎使用 mysql.:)
提前致谢.

Any help would be appreciated. Answers in sql server would be great but oracle & mysql is also welcome if they can point me in the right direction. :)
Thanks in advance.

推荐答案

Oracle 查询:

SELECT *
FROM   (
  SELECT client_id, priority, phone_number, phone_type
  FROM   client_table c
         LEFT OUTER JOIN
         phone_table p
         ON ( c.phone_id = p.phone_id )
)
PIVOT ( MAX( phone_type ) AS phonetype, MAX( phone_number ) AS phonenumber
        FOR priority IN ( 1 AS Prio1, 2 AS Prio2, 3 AS Prio3 ) );

输出:

 CLIENT_ID PRIO1_PHONETYPE PRIO1_PHONENUMBER PRIO2_PHONETYPE PRIO2_PHONENUMBER PRIO3_PHONETYPE PRIO3_PHONENUMBER
---------- --------------- ----------------- --------------- ----------------- --------------- -----------------
         1 C               9999999999        H               5555555555        W               7777777777        

这篇关于加入 2 个表并按 id、优先级和显示数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！