选择使用MySQL的父母和子女

本文介绍了选择使用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.