在循环中组合查询行

Question

我有以下 ColdFusion 9 代码：

<cfloop from="1" to="#arrayLen(tagArray)#" index="i">
    <cfquery name="qryGetSPFAQs" datasource="#application.datasource#">
        EXEC searchFAQ '#tagArray[i]#'
    </cfquery>
</cfloop>

EXEC 在数据库服务器上执行一个存储过程，该过程返回数据行，具体取决于参数是什么。我想做的是将查询合并到一个查询对象中。换句话说，如果它循环 3 次并且每次循环返回 4 行，我想要一个在一个对象中包含所有 12 行的查询对象。我该如何实现这一目标？

Answer 1

您可能想要采取不同的方法（修改存储过程以接受多个参数或使用列表和 fnSplit）并一次性返回数据集。但是，为了直接回答您的问题，您可以按照以下方式组合查询：

您可以在查询的查询中使用 UNION 来组合所有数据集。

<cfloop from="1" to="#arrayLen(tagArray)#" index="i">
    <cfquery name="qryGetSPFAQs#i#" datasource="#application.datasource#">
        EXEC searchFAQ '#tagArray[i]#'
    </cfquery>
</cfloop>

<cfquery name="combined" dbtype="query">
    <cfloop from="1" to="#arrayLen(tagArray)#" index="i">
        select * from qryGetSPFAQs#i#
        <cfif i lt arrayLen(tagArray)>UNION</cfif>
    </cfloop>
</cfquery>

Answer 2

更直接的方法可能是这样的：

<cfset bigQ = queryNew("column")>
<cfloop from="1" to="#arrayLen(tagArray)#" index="i">
    <cfquery name="qryGetSPFAQs" datasource="#application.datasource#">
        EXEC searchFAQ '#tagArray[i]#'
    </cfquery>
    <cfset queryAddRow(bigQ)>
    <cfset querySetCell(bigQ, "column". qryGetSPFAQs)>
</cfloop>

您需要为每列分配一个 querySetCell() 。查看实时文档中的查询函数更多信息。

Answer 3

这是一个开箱即用的解决方案，放弃了 SQL 视图的 StoredProc（我将解释）。

免责声明：在没有看到 SP 源代码的情况下，我无法判断我的解决方案是否适合。我假设 SP 是相当基本的，并且我承认我通常更喜欢 SP 的编译执行而不是视图，但是 SQL 视图的一次性执行应该胜过 SP 的循环 x 次。

首先创建一个看起来像 SP 中的 SELECT 语句的视图（当然，减去参数化——您将在新视图的 CFQUERY 内的 WHERE 子句中覆盖它。

其次，将循环设置为执行的操作不超过构建一个我们将用于 WHERE 子句的数据集，您需要使用 ArrayToList 和一些字符串操作来整理它，最终结果是存储在单个 CF 变量中的字符串，如下所示。 :

('ValueOfArrayElement1','ValueOfArrayElement2','Value_And_So_On')

使用 ArrayToList 的 delimeter 属性构建字符串非常简单，循环完成后，将左括号和单引号附加到最左边的位置。 string; 并将单引号和右括号附加到字符串的最右侧位置。

现在，编写 CFQUERY 语句以从视图中选择所需的列（而不是执行 SP）。对于 SP，您将在 CFQUERY 中放置一个 WHERE 子句。

哦，顺便说一句，我是说您需要一个 SQL 视图，但整个 SELECT 可以在 CFQUERY 中构建。就我个人而言，当我有一个多表 JOIN 时，我喜欢在 SQL 视图中定义它，这样它的执行速度比 CFQUERY 中的 JOIN 更快。最终 StoredProc 甚至更快，但我们的 WHERE 子句对于代码和读取来说比在不循环进出 SQL 的情况下发送到 StoredProc 更友好。

如果可能的话，最好只访问数据库一次并返回一次。这就是为什么我们循环遍历数组来写入一个等于数据集中所有值的字符串。这样，我们一次只会执行一个查询。

SELECT Col1, Col2, Col_etc
FROM SQL_View_Name
WHERE ColumnName in #BigStringWeMadeFromArrayToList#

当我们的 CFQUERY 被渲染时，该子句在 SQL 中看起来就像这样：

WHERE ColumnName in 
     ('ValueOfArrayElement1','ValueOfArrayElement2','Value_And_So_On')

所以你就明白了。就像我说的，这很好，因为它只需要访问一次数据库，而且由于我们正在构建一个视图，所以性能仍然相当不错——比运行 StoredProc 4 次以上要好。 （无意冒犯）

我必须重复...在没有看到 SP 代码的情况下，我不确定这是否可行。另外，为 SQL 视图（RDBMS 中的“较小”实体）放弃 StoredProc 有点奇怪，但我确信我们将实现更高的性能，而且我认为它也非常可读。