提高查询性能和可维护性

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
ref002   3
ref003   1

在第一个查询之后，我有一个foreach，它将迭代上面查询的结果。在foreach内部，对于上面查询的每个结果，我想获得一些在Table3中可用的信息，基本上，我想要每个代码(表1.Column 1)的表3列1中的所有值。因此，在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 (每个代码)获取所有的Table3.column1值。

怀疑

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

您知道在第一个查询中除了返回代码和计数之外，还需要为每个代码返回表3列1中的所有值吗？这样就可以删除foreach中的查询，并获取只需要一个查询(第一个查询)所需的所有内容？第一个查询的必要输出应该如下所示：

CODE     COUNT     IDNUMBERS
ref002    3         ab00, cd00
ref003    1         ef00

Answer 1

您的策略为每个代码生成一个查询，以及一个用于初始查询的查询，因此这确实是效率低下的。

一个简单的选项是在原始查询中使用字符串聚合函数来生成额外的列。

在Oracle中，您可以使用listagg()：

select 
    t1.column1 as code, 
    count(distinct t2.column1 || '|' || t2.column2) as cnt,
    listagg(t3.column1) idnumbers
from   table2 t2
inner join table3 t3 on t3.referencecolumn = t2.referencecolumn
inner join table1 t1 on t1.referencecolumn = t1.referencecolumn
where ...
group by t1.column1

listagg()有各种形式、重复管理和排序的选项--您可以找到详细信息在文件中。