一个SQL查询，还是一个循环中的许多查询？ [英] One SQL query, or many in a loop?

问题描述

我需要从表中提取几行并以两种方式处理它们：

I need to pull several rows from a table and process them in two ways:

• 聚集在键上


• 按行，按同一键排序

该表大致如下：

table (
   key,
   string_data,
   numeric_data
)

所以我正在研究两种编写函数的方法。

So I'm looking at two approaches to the function I'm writing.

第一个将使用一个查询来提取聚合数据，然后在循环内针对每个逐行数据集再次查询（以下是类似PHP的伪代码）：

The first would pull the aggregate data with one query, and then query again inside a loop for each set of row-by-row data (the following is PHP-like pseudocode):

$rows = query(
        "SELECT key,SUM(numeric_data)
         FROM table
         GROUP BY key"
    );

foreach ($rows as $row) {
    <process aggregate data in $row>

    $key = $row['key'];
    $row_by_row_data = handle_individual_rows($key);
}

function handle_individual_rows($key)
{
    $rows = query(
            "SELECT string_data
             FROM table WHERE key=?",
            $key
        );

    <process $rows one row at a time>

    return $processed_data;
}

或者，我可以做一个大查询并让代码完成所有工作：

Or, I could do one big query and let the code do all the work:

$rows = query(
    "SELECT key, string_data, numeric_data
     FROM table"
);

foreach ($rows as $row) {
    <process rows individually and calculate aggregates as I go>
}

在此应用程序中，性能并不是实际问题；我只是想编写明智且可维护的代码。

Performance is not a practical concern in this application; I'm just looking to write sensible and maintainable code.

我喜欢第一个选择，因为它更具模块化-我喜欢第二个选择，因为它在结构上看起来很简单。一种选择比另一种更好？还是真的只是样式问题？

I like the first option because it's more modular -- and I like the second option because it seems structurally simple. Is one option better than the other or is it really just a matter of style?

推荐答案

一个 SQL 查询。

这将

• 为您节省了往返数据库的大量时间


• 允许使用更有效的 GROUP BY 方法

• Save you lots of roundtrips to database
• Allow to use more efficient GROUP BY methods

由于数据库可以很好地执行聚合，因此可维护性也更好：您将所有结果集逻辑放在一个地方。

Since your aggregates may be performed equally well by the database, it will also be better for mainainability: you have all your resultset logic in one place.

下面是一个查询示例，该查询返回每一行并计算 SUM ：

Here is an example of a query that returns every row and calculates a SUM:

SELECT  string_data, numeric_data, SUM(numeric_data) OVER (PARTITION BY key)
FROM    table

请注意，这很可能将使用并行访问来为不同的键 c $ c>，很难在 PHP 中实现。

Note that this will most probably use parallel access to calculate SUM's for different key's, which is hardly implementable in PHP.

MySQL ：

SELECT  key, string_data, numeric_data,
        (
        SELECT  SUM(numeric_data)
        FROM    table ti
        WHERE   ti.key = to.key
        ) AS key_sum
FROM    table to

这篇关于一个SQL查询，还是一个循环中的许多查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！