从MySQL的同一张表中获取父子关系的所有数据 [英] Get all data of parent child relation-ship from same table in mysql

问题描述

我正在尝试获取符合父子关系船的所有行. 例如

I am trying to get all rows which meets the parent child relation ship. for example

  id         Title    parent_id
  1200        A       1000
  1201        B       1000
  1202        C       1000
  1203        D       1000
  1204        E       1200
  1205        F       1200
  1206        G       1201
  1207        H       1205
  1208        I       1205
  1209        J       1205

现在我有1209 id，我想检索满足1209的父子关系船的所有行.像这里的1209与1205有关，1205与1200有关，而1200与1000有关.

Now i have 1209 id, I want to retrieve all rows which satisfy parent child relation ship for 1209. Like here 1209 relates to 1205, 1205 relates to 1200, and 1200 relates to 1000.

我尝试了此查询

  SELECT * FROM `category` a left join category b on a.id=b.parent_id where a.id=1209

但这只给了我一个记录.我们正在尝试获取此关系中的所有行.

But this is giving me only one record. We are trying to get all rows which comes in this relationship.

推荐答案

如果您只是在寻找父母，祖父母或外祖父母，则可以使用类似的方法.

if you're just looking for it's parent,grandparent,greatgrand parent you can use something like this.

SELECT id,title,parent_id FROM
    (SELECT id,title,parent_id,
       CASE WHEN id = 1209 THEN @id := parent_id
            WHEN id = @id THEN @id := parent_id
            END as checkId
     FROM Test
     ORDER BY id DESC) as T
WHERE checkId IS NOT NULL

sqlfiddle

以防万一，如果您想查找所有具有id的子代，孙子或曾孙子，则可以使用此

And just in case if you wanted to find all children, and grand children or great grand children of an id you can use this

SELECT id,title,parent_id FROM
    (SELECT id,title,parent_id,
            CASE WHEN id = 1200 THEN @idlist := CONCAT(id)
                 WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id)
            END as checkId
     FROM Test
     ORDER BY id ASC) as T
WHERE checkId IS NOT NULL

找到孩子的sqlfiddle

查询以查找多个孩子的所有父母/祖父母/曾祖父母

query for finding all parents/grandparents/greatgrandparents of multiple children

SELECT id,title,parent_id FROM
(SELECT id,title,parent_id,
       CASE WHEN id in (1209,1206) THEN @idlist := CONCAT(IFNULL(@idlist,''),',',parent_id)
            WHEN FIND_IN_SET(id,@idlist) THEN @idlist := CONCAT(@idlist,',',parent_id)
            END as checkId
FROM Test
ORDER BY id DESC)T
WHERE checkId IS NOT NULL

sqlfiddle

这篇关于从MySQL的同一张表中获取父子关系的所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！