SQL父/子递归调用或联合？

我似乎无法找到相关的例子.

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

父表列:PK_ID,Column1,Column2,FK1

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

最终结果集

3,col1text,col2text,1(child)
5,col1texta,col2texta,2(child)
6,col1textb,col2textb,0(child)
9,col1textc,col2textc,4(child)

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

1> cletus..：

---

好吧,显然,基于对另一个答案的赞成,这需要进一步解释.示例(使用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

错误.忽略没有孩子的情况.

左外连接:

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

也正确.

好的,那么使用哪个？计划只告诉你这么多.问题的子查询VS左加入是一个古老的,而且也没有明确的答案,而不基准它.所以我们需要一些数据:

\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 "

\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 ( ... ),第二个是原始数据.

建立:

MySQL 5.0使用PremiumSoft Navicat(LIMIT 10000查询中);

SQL Server Express 2005使用Microsoft SQL Server Management Studio Express;

Oracle XE使用PL/SQL Developer 7(限制为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.297s/2.656s

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

只是去展示真实数据库的价值.此外,删除SomeText字段对MySQL的性能有重大影响.此外,在10000的限制与没有使用MySQL之间没有太大的区别(将性能提高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.844s/11.10s

SQL Server: 0s/6s

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供应商,差异可能非常显着.