mysql 处理树

Question

我得到了这张表:

CREATE TABLE 'category' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'parent_category_id' INT(11) NULL DEFAULT NULL,
    'name' VARCHAR(100) NOT NULL,
    PRIMARY KEY ('id'),
    INDEX 'parent_category_id' ('parent_category_id'),
    CONSTRAINT 'category_ibfk_1' FOREIGN KEY ('parent_category_id') REFERENCES 'category' ('id')
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

如何选择子类别少于 3 个的类别(无深度)，以及如何选择没有子元素的类别。谢谢!

Answer 1

少于三个:

select parent.*
from category parent left outer join
     category child
     on parent.id = child.parent_category_id
group by parent.id
having count(child.id) < 3

对于没有类别:

select parent.*
from category parent left outer join
     category child
     on parent.id = child.parent_category_id
where child.id is null