子查询以提高性能和可维护性

Question

我有一个这样的查询：

select Table1.column1 AS CODE, COUNT(DINSTINCT(Table2.column1 || '|' || Table2.column2)) AS COUNT
FROM   Table2
INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn
INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn
WHERE
....
AND Table1.column1 <> ''

查询的输出将是这样的，基本上是一个CODE和相应的COUNT，例如：

CODE    COUNT
ref002   3
ref003   1

在第一个查询之后，我有一个foreach，它将迭代上面查询的结果。在foreach内部，对于查询的每个结果上面，我想获得表3中可用的一些信息，基本上，我想要每个CODE(表1.第1列(的表3.column1中的所有值。因此，在foreach中，我有另一个查询来获得每个迭代结果的表3.列：

select Table3.column1
FROM   Table3
INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn
INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn
WHERE .... 
AND Table1.column1 = (equal to a parameter (Table1.column1) that is available in each foreach iteration)

像这样，我可以获得第一个查询的每个Table1.column1(每个CODE(的所有Table3.column1值。

怀疑

两个查询几乎相同。foreach之前的查询和foreach内部的查询之间的唯一区别在于SELECT部分和WHERE部分，基本上是where只有一个附加条件。因此，在性能和可维护性方面，这两个查询似乎不是很好，因为两个查询几乎相同。因此，应该可以在第一个查询中获得所有必要的信息，而不是在foreach中有第二个查询。

你知道在第一个查询中除了返回CODE和COUNT之外，还需要为每个CODE返回Table3.column1中的所有值吗？因此，可以删除foreach中的查询，只获得一个查询(第一个查询(所需的所有内容吗？第一个查询的必要输出应该类似于：

CODE     COUNT     IDNUMBERS
ref002    3         ab00, cd00
ref003    1         ef00

也许select子句中的子查询可以解决这个问题，但我不知道如何正确地使用子查询。是否需要使用第一个查询，并将完整的第二个查询作为子查询放在select子句中？类似：

select 
Table1.column1 AS CODE, 
COUNT(DINSTINCT(Table2.column1 || '|' || Table2.column2)) AS COUNT, 
(select Table3.column1 
FROM Table3 INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn 
INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn 
WHERE .... 
AND Table3.column1 = Table1.column1) AS IDNUMBERS 
FROM Table2 
INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn 
INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn 
WHERE .... 

Answer 1

您可能正在寻找collect()函数，它为您提供了一个嵌套表作为主要结果集的一部分。

下面是一个使用默认HR模式数据的示例：

select d.department_id,
count(e.employee_id) as cnt,
cast(collect(e.first_name) as sys.odcivarchar2list) as names
from departments d
left join employees e on e.department_id = d.department_id
group by d.department_id;
DEPARTMENT_ID        CNT NAMES                                                                           
------------- ---------- --------------------------------------------------------------------------------
10          1 ODCIVARCHAR2LIST('Jennifer')                                                    
20          2 ODCIVARCHAR2LIST('Michael', 'Pat')                                              
30          6 ODCIVARCHAR2LIST('Den', 'Karen', 'Guy', 'Sigal', 'Shelli', 'Alexander')         
40          1 ODCIVARCHAR2LIST('Susan')                                                       
...

您还可以使用cursor()表达式为每个结果行包含一个子查询，这更接近您最初的建议：

select d.department_id,
count(e.employee_id) as cnt,
cursor(
select e2.first_name
from employees e2
where e2.department_id = d.department_id
) as names
from departments d
left join employees e on e.department_id = d.department_id
group by d.department_id;

SQL Developer将其结果(作为脚本运行时(显示为：

DEPARTMENT_ID        CNT NAMES                                                                           
------------- ---------- --------------------------------------------------------------------------------
10          1 CURSOR STATEMENT : 3                                                            
CURSOR STATEMENT : 3
FIRST_NAME          
--------------------
Jennifer
20          2 CURSOR STATEMENT : 3                                                            
CURSOR STATEMENT : 3
FIRST_NAME          
--------------------
Michael
Pat
30          6 CURSOR STATEMENT : 3                                                            
CURSOR STATEMENT : 3
FIRST_NAME          
--------------------
Den
Alexander
Shelli
Sigal
Guy
Karen
6 rows selected. 
...

在这种情况下，当您在外部查询和子查询中访问相同的表时，可能效率较低；另一方面，您正在提取相同的数据块，所以这可能无关紧要。不过，处理ref游标可能比处理集合更简单。这在一定程度上取决于你现在如何处理结果。

---

您最初的建议基本上是没有cursor关键字的查询；但这将出错，因为标量子查询表达式必须返回一个值：

select d.department_id,
count(e.employee_id) as cnt,
(
select e2.first_name
from employees e2
where e2.department_id = d.department_id
) as names
from departments d
left join employees e on e.department_id = d.department_id
group by d.department_id;
ORA-01427: single-row subquery returns more than one row

除非我(非常人为地(将其限制在我事先知道的部门，否则只会在子查询中返回一行：

select d.department_id,
count(e.employee_id) as cnt,
(
select e2.first_name
from employees e2
where e2.department_id = d.department_id
) as names
from departments d
left join employees e on e.department_id = d.department_id
where d.department_id in (10, 40)
group by d.department_id;
DEPARTMENT_ID        CNT NAMES                                                                           
------------- ---------- --------------------------------------------------------------------------------
10          1 Jennifer                                                                        
40          1 Susan