mysql - 在表中查找每个类别的深度

Question

我有一个名为 Categories 的表，其结构如下:

CREATE TABLE `Categories` (
    `CatID` int(11) NOT NULL AUTO_INCREMENT,
    `UserID` int(11) NOT NULL,
    `CatName` varchar(250) NOT NULL,
    `Parent` int(200) NOT NULL,
    PRIMARY KEY (`CatID`)
)

我想创建一个 SQL 语句来查找每个类别的深度，但我无法弄清楚这个。任何帮助将不胜感激。

Answer 1

问题的根源在于您的数据库结构并不是为了解决这个问题。如果我处在你的位置，我会在两个单独的表中重组该表(称为闭包表的解决方案)。

假设您使用 MySQL，<rant>与 linux 一样，从未指定发行版的人不知道 ubuntu 不是唯一的发行版 </rant> .

CREATE TABLE Categories (
   node_id INT AUTO_INCREMENT PRIMARY KEY ,
   label VARCHAR(40) NOT NULL
);

CREATE TABLE Closure (
   ancestor INT NOT NULL,
   descendant INT NOT NULL,
   PRIMARY KEY (ancestor, descendant),
   FOREIGN KEY (ancestor) REFERENCES Nodes(node),
   FOREIGN KEY (descendant) REFERENCES Nodes(node)
);

SELECT count( Categories.node_id ) AS depth
FROM Closure AS Des
     JOIN Closure AS Anc ON (Anc.descendant = Des.descendant)
     JOIN Categories ON ( Categories.node = Anc.ancestor)
WHERE Des.ancestor = 1 AND Des.descendant != Des.ancestor
GROUP BY Des.descendant;

这将计算每个节点的深度。在这个设置中，我实际上可以让每个子类别成为多个类别的后代。

您可以找到有关闭包表的更多信息 in this blog post ，或犒赏自己一本名为 "SQL Antipatterns" 的书.