选择使用MySQL的父母和子女 [英] Select Parent and Children With MySQL

问题描述

我知道这个问题经常出现，但是今天我找不到想要的答案.我有一个带有这种模式的表.

I know this question comes up often, but today I can't find the answer I'm looking for. I have a table with this schema.

CREATE TABLE `comments` (
    `id` bigint(10) unsigned not null auto_increment,
    `parent_id` bigint(10) unsigned default 0,
    `date_sent` datetime not null,
    `content` text not null,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

我想选择父行以及这些行的子行.我不允许孩子生孩子，所以只有一个父母，可以有任意数量的孩子.

I'd like to select parent rows, and the children of those rows. I don't allow children to have children, so it's just one parent, with any number of children.

我想我已经用联合或内部联接做到了这一点.

I think I've seen this done with unions before, or inner joins.

推荐答案

父母是没有parent_id的记录.
子级的parent_id等于父级注释的id.

Parents are records with no parent_id.
Children have parent_id equal to the parent comment's id.

  SELECT ...
    FROM comments AS parent
         LEFT JOIN comments AS child 
         ON child.parent_id = parent.id
   WHERE parent.parent_id IS NULL
ORDER BY parent.id, child.id;

请注意，自连接应该是外部连接，这样您就不会错过没有孩子的父注释.

Note that the self-join should be an outer join so that you don't miss parent comments with no children.

这篇关于选择使用MySQL的父母和子女的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！