代码之家 › 专栏 › 技术社区 › ryvantage

Pivot在Oracle中的应用

pivot oracle sql

ryvantage · 技术社区 · 6 年前

我有四个表(Oracle):

party
----------------
key

person
----------------
party_key | name

organization
----------------
party_key | name

id
----------------
party_key | id (varchar)

在“id”表中,每个组织有多个(超过1个)id,每个人有一个(不是我的业务/数据模型设计——对此没有控制权)。

SELECT pers.name as person_name, org.name as org_name, id_pers.id as person_id, id_org.id as org_id
FROM party part
INNER JOIN person pers ON pers.party_key = part.key
INNER JOIN organization org ON org.party_key = part.key
INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
INNER JOIN id id_org ON pers.party_key = id_org.party_key

产生以下结果:

person_name |   org_name | person_id | org_id
       John | whitehouse |     00005 |   0001
       John | whitehouse |     00005 |   0002
   Samantha | whitehouse |     00007 |   0001
   Samantha | whitehouse |     00007 |   0002
       John |    library |     00005 |   0008
       John |    library |     00005 |   0009
   Samantha |    library |     00007 |   0008
   Samantha |    library |     00007 |   0009

person_name |   org_name | person_id | org_id1 | org_id2
       John | whitehouse |     00005 |    0001 |    0002
   Samantha | whitehouse |     00007 |    0001 |    0002
       John |    library |     00005 |    0008 |    0009
   Samantha |    library |     00007 |    0008 |    0009

我认为解决办法包括 pivot 但我不知道如何执行它。

2 回复 | 直到 6 年前

Fahmi 6 年前

将最小值和最大值与分组依据一起使用:

    select person_name, org_name,person_id, min(org_id), max(org_id)
    from 
   (SELECT pers.name as person_name, org.name as org_name, id_pers.id as person_id, id_org.id as org_id
FROM party part
INNER JOIN person pers ON pers.party_key = part.key
INNER JOIN organization org ON org.party_key = part.key
INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
INNER JOIN id id_org ON pers.party_key = id_org.party_key)a
    group by person_name, org_name,person_id

MT0 6 年前

我不确定你的桌子结构,也不确定你是否总是坐在那里 party_key PIVOT (您首先必须使用 ROW_NUMBER 分析函数):

SQL Fiddle

CREATE TABLE party ( key ) AS
  SELECT 1 FROM DUAL;

CREATE TABLE person (party_key, name ) AS
  SELECT 1, 'John'     FROM DUAL UNION ALL
  SELECT 1, 'Samantha' FROM DUAL;

CREATE TABLE organization ( party_key, name ) AS
  SELECT 1, 'Whitehouse' FROM DUAL UNION ALL
  SELECT 1, 'Library'    FROM DUAL;


CREATE TABLE id ( party_key, id ) AS
  SELECT 1, '0001' FROM DUAL UNION ALL
  SELECT 1, '0002' FROM DUAL;

问题1 :

SELECT *
FROM   (
  SELECT pers.name as person_name,
         org.name as org_name,
         id_pers.id as person_id,
         id_org.id as org_id,
         ROW_NUMBER() OVER (
           PARTITION BY pers.name, org.name, id_pers.id
           ORDER BY id_org.id
         ) AS rn
  FROM   party part
         INNER JOIN person pers ON pers.party_key = part.key
         INNER JOIN organization org ON org.party_key = part.key
         INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
         INNER JOIN id id_org ON pers.party_key = id_org.party_key
)
PIVOT ( MAX( org_id ) FOR rn IN (
  1 AS org_id1,
  2 AS org_id2
) )

Results

| PERSON_NAME |   ORG_NAME | PERSON_ID | ORG_ID1 | ORG_ID2 |
|-------------|------------|-----------|---------|---------|
|        John |    Library |      0001 |    0001 |    0002 |
|        John |    Library |      0002 |    0001 |    0002 |
|        John | Whitehouse |      0001 |    0001 |    0002 |
|        John | Whitehouse |      0002 |    0001 |    0002 |
|    Samantha |    Library |      0001 |    0001 |    0002 |
|    Samantha |    Library |      0002 |    0001 |    0002 |
|    Samantha | Whitehouse |      0001 |    0001 |    0002 |
|    Samantha | Whitehouse |      0002 |    0001 |    0002 |