sql - 在 Oracle 中透视结果
问题描述
我有四个表(Oracle):
party
----------------
key
person
----------------
party_key | name
organization
----------------
party_key | name
id
----------------
party_key | id (varchar)
在“id”表中,每个组织有多个(超过 1 个)“id”,每个人有一个(不是我的业务/数据模型设计——无法控制)。
所以这个sql查询:
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
但我想要一个 SQL 查询来产生这样的东西:
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
但我不确定如何执行它。
解决方案
我不确定你的表结构和总是加入party_key
工作......但这里是如何使用 a PIVOT
(你首先必须使用ROW_NUMBER
分析函数为每个组织 id 分配一个行号):
Oracle 11g R2 模式设置:
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
) )
结果:
| 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 |
推荐阅读
- spring - 从内存数据库中的 H2 切换到 SQL Server 时出错
- c++ - c++ - 成功打印超出范围的字符串索引
- symfony - 虚拟多对多关系
- pandas-groupby - GroupBy 数据框并找出另一列的最大出现次数
- google-apps-script - 使用谷歌脚本创建带有关键字的时间戳
- linux - Linux读取外盘数据,无法挂载
- node.js - 使用相同的 package.json 文件定义多运行配置
- cordova - Cordova 支持 finally 方法吗?
- php - PHP登录页面为空白而不是回显结果
- mysql - 使用 MySQL 配置 ejabberd 时出错