mysql - SQL:遍历同一表

我有一个表“projectdetails”，其中parent_id是同一表中project_id列的外键。。
从下面的记录中，我只希望parent_id的行有行，没有'recycle_bin'值为1，并且应该显示creater_id=7923的记录；
我有这样的记录：

mysql> SELECT *FROM  projectdetails;
+------------+-----------------+-----------+-------------+------------+
| project_id | project_name    | parent_id | recycle_bin | creater_id |
+------------+-----------------+-----------+-------------+------------+
|          0 | -               |         0 |           0 |       7898 |
|        100 | Country         |         0 |           2 |       7923 |
|        101 | animal          |         0 |           1 |       7923 |
|        102 | India           |       100 |           2 |       7923 |
|        103 | pakistan        |       100 |           2 |       7923 |
|        104 | cow             |       101 |           1 |       7923 |
|        105 | elephant        |       101 |           1 |       7923 |
|        109 | black elephent  |       105 |           1 |       7923 |
|        110 | white elephent  |       105 |           2 |       7923 |
|        111 | wild black elep |       109 |           1 |       7923 |
|        112 | simple blak elp |       109 |           1 |       7923 |
|        113 | lion            |       105 |           1 |       7923 |
|        114 | red lion        |       113 |           1 |       7923 |
|        115 | black lion      |       113 |           1 |       7923 |
|        116 | girls           |         0 |           1 |       7923 |
|        117 | good girls      |       116 |           1 |       7923 |
|        118 | funky girls     |       116 |           1 |       7923 |
+------------+-----------------+-----------+-------------+------------+
7 rows in set (0.00 sec)

预期产量：

+------------+----------------+-----------+-------------+------------+
| project_id | project_name   | parent_id | recycle_bin | creater_id |
+------------+----------------+-----------+-------------+------------+
|        100 | Country        |         0 |           2 |       7923 |
|        110 | white elephent |       105 |           2 |       7923 |
+------------+----------------+-----------+-------------+------------+

注意：这是树结构表，在这里每个子id用户可以插入许多其他记录。。就像树的结构一样。所以请回答这个问题。
如果你不能得到我所要求的，那么请写评论，我会尽力向你解释。。。
更新
所以基本上，当查询读取一行来决定它是否适用于show时，
第一个查询流程：比如国家。
首先查找一行，如INDIA，然后查看其父项id，如果它有父项id，则转到该父项id（现在这是一个项目id=100），然后再次查看它是否有父项，如果没有，则查看列recycle bin。。如果1，则显示此结果，否则忽略。

最佳答案

像这样的？FIDDLE供参考

SELECT project_id,project_name,parent_id,recycle_bin,creater_id
FROM projectdetails AS pd1
JOIN(
    SELECT parent_id FROM projectdetails as pd
    WHERE pd.parent_id > 0 AND pd.recycle_bin > 1
) AS t ON t.parent_id = pd1.project_id

使用新数据。。NEW_FIDDLE

SELECT
  if(pd1.recycle_bin >1, pd1.project_id, t.project_id) AS project_id,
  if(pd1.recycle_bin >1, pd1.project_name, t.project_name) AS project_name,
  if(pd1.recycle_bin >1, pd1.parent_id, t.parent_id) AS parent_id,
  if(pd1.recycle_bin >1, pd1.recycle_bin, t.recycle_bin) AS recycle_bin,
  if(pd1.recycle_bin >1, pd1.creater_id, t.creater_id) AS creater_id
FROM projectdetails AS pd1
JOIN(
  SELECT * FROM projectdetails AS pd
  WHERE pd.parent_id > 0
    AND pd.recycle_bin > 1
) AS t ON t.parent_id = pd1.project_id
GROUP BY project_id

不使用IFLAST_FIDDLE

SELECT
   pd1.project_id,
   pd1.project_name,
   pd1.parent_id,
   pd1.recycle_bin,
   pd1.creater_id
FROM projectdetails AS pd1
WHERE NOT EXISTS(
  SELECT pd.recycle_bin FROM projectdetails as pd
  WHERE pd1.parent_id = pd.project_id
    AND pd.recycle_bin > 1
) AND pd1.creater_id = 7923 and pd1.recycle_bin > 1
GROUP BY pd1.project_id;

关于mysql - SQL:遍历同一表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23113118/