父级-单个表中的子级关系 [英] Parent - Child relationship within a single table

问题描述

我有一个如下表.

parentid   uid
10001      10001
10001      10002
10001      10003
10002      10004
10004      10005
10003      10006
10005      10007

我需要在此单个表中的行之间建立父子关系.

我需要以相反的顺序直到4个级别来获取父项.例如，最后一个记录是 uid 10007 ，其父代是10005 .现在 uid 10005 的父级是 10004 ，而 10004 的父级是 10002 和 10002 的父母是 10001 .

我正在使用MySQL，因此似乎无法进行递归.我有哪些选择，以及如何解决这个多层次的问题.我使用PHP/MySQL.

预先感谢大家.

解决方案

由于您只有4个级别，因此您不需要递归(尽管使用MS SQL CTE这样会很方便)./p>

类似的东西:

SELECT
  t4.uid as child, 
  --t3.uid as parent,
  --t2.uid as grand_parent,
  --t1.uid as great_grand_parent,
  t1.parentid as great_great_grand_parent
FROM
  your_table_name t1

  inner join your_table_name t2
  on t2.parentid = t1.uid

  inner join your_table_name t3
  on t3.parentid = t2.uid

  inner join your_table_name t4
  on t4.parentid = t3.uin

where 
  t4.uid = '10007' -- your start node.

如果您需要对多个节点执行此操作，则需要将其加入到选择起始节点的对象中，或者例如将上面的WHERE t4.uid = '10007'子句替换为WHERE t4.uid IN (SELECT DISTINCT uid FROM your_table_name)

这是徒手完成的，因此对错别字表示歉意.

I have a table which is like the following.

parentid   uid
10001      10001
10001      10002
10001      10003
10002      10004
10004      10005
10003      10006
10005      10007

I need to establish the parent child relationship between rows in this single table.

I need to get the parent in the reverse order till 4 levels. For example the last record is uid 10007 whose parentid is 10005. Now uid 10005's parent is 10004 and 10004's parent is 10002 and 10002's parent is 10001.

I am using MySQL so recursion seems to be not possible. What are the options that I have and how do I address this multi-level issue. I use PHP/MySQL.

Thanks in advance guys.

解决方案

Since you have a finite 4 levels, you shouldn't need recursion (although it'd be handy to be able to use eg MS SQL CTEs).

Something like:

SELECT
  t4.uid as child, 
  --t3.uid as parent,
  --t2.uid as grand_parent,
  --t1.uid as great_grand_parent,
  t1.parentid as great_great_grand_parent
FROM
  your_table_name t1

  inner join your_table_name t2
  on t2.parentid = t1.uid

  inner join your_table_name t3
  on t3.parentid = t2.uid

  inner join your_table_name t4
  on t4.parentid = t3.uin

where 
  t4.uid = '10007' -- your start node.

If you need to do this for multiple nodes, you'd need to join this to something that selects your start nodes, or eg replace the above WHERE t4.uid = '10007' clause to be WHERE t4.uid IN (SELECT DISTINCT uid FROM your_table_name)

This was done freehand so apologies for typos.

这篇关于父级-单个表中的子级关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！