SQL父/子递归调用还是联合？

Question

我似乎找不到相关的例子。

我正在尝试返回表的子集，对于该表中的每一行，我想检查它有多少个子项，并将该数字作为结果集的一部分返回。

父表列:PK_ID、列 1、列 2、FK1

对于结果集中的每个 FK1，从 child_table 中选择 count(*)。

最终结果集

3、col1text、col2text、1(子级)
5、col1texta、col2texta、2(子)
6、col1textb、col2textb、0(子级)
9、col1textc、col2textc、4(子)

我正在努力寻找在另一个查询中引用结果集中的列，然后再次将它们连接在一起的最佳方法。使用T-sql

Answer 1

好吧，显然，根据其他答案的赞成票，这需要进一步解释。示例(使用 MySQL 完成，因为我有它，但原理对于任何 SQL 方言都是通用的):

CREATE TABLE Blah (
  ID INT PRIMARY KEY,
  SomeText VARCHAR(30),
  ParentID INT
)

INSERT INTO Blah VALUES (1, 'One', 0);
INSERT INTO Blah VALUES (2, 'Two', 0);
INSERT INTO Blah VALUES (3, 'Three', 1);
INSERT INTO Blah VALUES (4, 'Four', 1);
INSERT INTO Blah VALUES (5, 'Five', 4);

左连接版本:

SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText

错了。忽略没有 child 的情况。

左外连接:

SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText

错误，原因有些微妙。 COUNT(1) 对 NULL 行进行计数，而 COUNT(b.ID) 则不会。所以上面是错误的，但这是正确的:

SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText

相关子查询:

SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a

也正确。

好的，那么该使用哪个呢？计划只能告诉你这么多。 subqueries vs left-joins的问题是一个古老的问题，如果不对其进行基准测试就没有明确的答案。所以我们需要一些数据:

<?php
ini_set('max_execution_time', 180);

$start = microtime(true);

echo "<pre>\n";

mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo mysql_error();
    exit();
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo mysql_error();
    exit();
}

$count = 0;
$limit = 1000000;
$this_level = array(0);
$next_level = array();

while ($count < $limit) {
    foreach ($this_level as $parent) {
        $child_count = rand(0, 3);
        for ($i=0; $i<$child_count; $i++) {
            $count++;
            query("INSERT INTO Blah (ID, SomeText, ParentID) VALUES ($count, 'Text $count', $parent)");
            $next_level[] = $count;
        }
    }
    $this_level = $next_level;
    $next_level = array();
}

$stop = microtime(true);
$duration = $stop - $start;
$inserttime = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $inserttime seconds.\n";
echo "</pre>\n";

function query($query) {
    mysql_query($query);
    if (mysql_error()) {
        echo mysql_error();
        exit();
    }
}
?>

在这次运行中我耗尽了内存 (32M)，所以最终只得到了 876,109 条记录，但是嘿它就可以了。后来，当我测试 Oracle 和 SQL Server 时，我采用了完全相同的数据集并将其导入到 Oracle XE 和 SQL Server Express 2005 中。

现在另一张海报提出了我在查询周围使用计数包装器的问题。他正确地指出，在这种情况下优化器可能不会执行子查询。 MySQL 似乎没有那么聪明。甲骨文是。 SQL Server 似乎也是如此。

因此，我将为每个数据库查询组合引用两个数字:第一个包含在 SELECT COUNT(1) FROM ( ... ) 中，第二个是原始数据。

设置:

• 使用 PremiumSoft Navicat 的 MySQL 5.0(查询中LIMIT 10000)；
• 使用 Microsoft SQL Server Management Studio Express 的 SQL Server Express 2005；
• 使用 PL/SQL Developer 7 的 Oracle XE(限制为 10,000 行)。

左外连接:

SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText

• MySQL:5.0:51.469s/49.907s
• SQL Server: 0⁽¹⁾/9s⁽²⁾
• Oracle XE:1.297 秒/2.656 秒

(1) 几乎瞬时(确认不同的执行路径)
(2) 令人印象深刻的是，它返回的是所有行，而不是 10,000

只是为了展示真实数据库的值(value)。此外，删除 SomeText 字段对 MySQL 的性能也有重大影响。此外，MySQL 的限制为 10000 和没有限制之间没有太大区别(性能提高了 4-5 倍)。 Oracle 之所以有这个问题，只是因为 PL/SQL Developer 在达到 100M 内存使用量时崩溃了。

相关子查询:

SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a

• MySQL:8.844 秒/11.10 秒
• SQL Server:0 秒/6 秒
• Oracle:0.046s/1.563s

因此，MySQL 的性能提高了 4-5 倍，Oracle 的速度大约是其两倍，而 SQL Server 可能只是快了一点。

重点仍然是:相关子查询版本在所有情况下都更快。

相关子查询的另一个优点是它们在语法上更清晰并且更容易扩展。我的意思是，如果您想在一堆其他表中进行计数，则每个表都可以干净而轻松地作为另一个选择项包含在内。例如:想象一下客户的发票记录，其中这些发票要么是未付的，要么是逾期的，要么是已付的。使用简单的子查询:

SELECT id,
  (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'UNPAID') unpaid_invoices,
  (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'OVERDUE') overdue_invoices,
  (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'PAID') paid_invoices
FROM customers c

聚合版本要丑陋得多。

现在我并不是说子查询总是优于聚合连接，但通常情况下，您必须对其进行测试。根据您的数据、数据的大小以及您的 RDBMS 供应商，差异可能非常显着。