oracle 嵌套SQL查询或子查询

gwo2fgha 于 2023-03-22 发布在 Oracle

关注(0)|答案(1)|浏览(215)

我在三个现有的表上创建了以下查询，它也可以正常工作。

组织表

-------------
id    name        service
--------------------------------
1     CEO            1, 2, 4
2     ALF            1, 3, 5
3     FRGT           
4     OFFICE_IT      1, 2, 7

城市表

-------------
id    name
-------------
10     Berlin
20     Paris
30     London
40     Rom

关系表

一个二个一个一个
新视图：

id    Organization    service      City
-----------------------------------------------
1     CEO              1, 2, 4      Berlin
2     ALF              1, 3, 5      Paris
4     OFFICE_IT        1, 7         Rom

但现在我想设计的表在这样一种方式，即价值观在“服务”列中的字符串的每个字符串都单独写在一行中。
请参见示例：

id    Organization    service      City
-----------------------------------------------
1     CEO              1            Berlin
1     CEO              2            Berlin
1     CEO              4            Berlin
2     ALF              1            Paris
2     ALF              3            Paris
2     ALF              5            Paris
4     OFFICE_IT        1            Rom
4     OFFICE_IT        7            Rom

在此期间，我设法得到了一个相应的查询“组织”表只，没有关系的城市。

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
  FROM Organization o ,
    xmltable(('"'
    || REPLACE(o.service, ',', '","')
    || '"'))

然而，我目前缺乏这样的想法将两个单独的查询压缩到一个SQL查询中。您有解决方案吗？

oracle

来源：https://stackoverflow.com/questions/75767480/nested-sql-query-or-subquery

1条答案

按热度按时间

ngynwnxp1#

您可以将逗号连接替换为CROSS APPLY（来自Oracle 12）：

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS APPLY xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL

或CROSS JOIN（在早期版本中）：

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS JOIN xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL

或者，如果逗号联接在ANSI联接之后，则可以混合使用ANSI联接语法和Oracle的旧版逗号联接语法：

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
WHERE  o.service IS NOT NULL

或者，您可以在整个过程中使用传统的逗号连接：

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"')),
       Relation rel, 
       City c
WHERE  o.service IS NOT NULL 
AND    o.id = rel.oid
AND    rel.cid  = c.id

其中，对于示例数据：

CREATE TABLE Organization (id, name, service) AS
SELECT 1, 'CEO',       '1, 2, 4' FROM DUAL UNION ALL
SELECT 2, 'ALF',       '1, 3, 5' FROM DUAL UNION ALL
SELECT 3, 'FRGT',      NULL      FROM DUAL UNION ALL
SELECT 4, 'OFFICE_IT', '1, 2, 7' FROM DUAL;

CREATE TABLE City (id, name) AS
SELECT 10, 'Berlin' FROM DUAL UNION ALL
SELECT 20, 'Paris'  FROM DUAL UNION ALL
SELECT 30, 'London' FROM DUAL UNION ALL
SELECT 40, 'Rome'   FROM DUAL;

CREATE TABLE Relation (oid, cid) AS
SELECT 1, 10 FROM DUAL UNION ALL
SELECT 2, 20 FROM DUAL UNION ALL
SELECT 3, 30 FROM DUAL UNION ALL
SELECT 4, 40 FROM DUAL;

所有输出：
| 识别号|姓名|服务|城市|
| - ------|- ------|- ------|- ------|
| 1个|首席执行官|1个|柏林|
| 1个|首席执行官|第二章|柏林|
| 1个|首席执行官|四个|柏林|
| 第二章|ALF|1个|巴黎|
| 第二章|ALF|三个|巴黎|
| 第二章|ALF|五个|巴黎|
| 四个|办公_IT|1个|罗马|
| 四个|办公_IT|第二章|罗马|
| 四个|办公_IT|七|罗马|
fiddle

赞(0）回复(0）举报 2023-03-22

我来回答

oracle 嵌套SQL查询或子查询

1条答案

相关问题

热门标签

最新问答