MySQL递归查询

Question

以下查询

WITH RECURSIVE cte AS
(
  SELECT id, CAST(name AS CHAR(200)) AS name, parentid, icon, url, position,
         CAST(id AS CHAR(200)) AS path,
         0 as depth
  FROM categories WHERE parentid = 46
  UNION ALL
  SELECT c.id,
         c.name,
         c.parentid,
         c.icon,
         c.url,
         c.position,
         CONCAT(cte.path,',', c.id),
         cte.depth+1
  FROM categories c JOIN cte ON
  cte.id=c.parentid
)
SELECT id, path FROM cte ORDER BY id;

这将返回两列：ID和Path。如果ID为" 52 "，则路径为" 50 , 51 ,52“，其中50和51是父母和祖父母，52是ID本身。如果没有"52“id，我如何才能得到"50,51”的结果？谢谢！

​

<table>
<thead>
<tr>
<th>ID </th>
<th>Parentid</th>
</tr>
</thead>
<tbody>
<tr><td>    46  </td><td>   0   </td></tr>
<tr><td>    47  </td><td>   46  </td></tr>
<tr><td>    48  </td><td>   46  </td></tr>
<tr><td>    49  </td><td>   46  </td></tr>
<tr><td>    50  </td><td>   46  </td></tr>
<tr><td>    51  </td><td>   50  </td></tr>
<tr><td>    52  </td><td>   51  </td></tr>
<tr><td>    53  </td><td>   47  </td></tr>
<tr><td>    54  </td><td>   47  </td></tr>
<tr><td>    55  </td><td>   47  </td></tr>
<tr><td>    56  </td><td>   48  </td></tr>
<tr><td>    57  </td><td>   51  </td></tr>
<tr><td>    58  </td><td>   49  </td></tr>
<tr><td>    60  </td><td>   50  </td></tr>
</tbody>
</table>

​

Answer 1

在构建path字段值时，您似乎正在尝试排除那些不是父ID的ID。

可以使用条件select子句获得条件WHEN。

WITH RECURSIVE cte AS
(
  SELECT id, CAST(name AS CHAR(200)) AS name, parentid, icon, url, position,
         CAST(id AS CHAR(200)) AS path,
         0 as depth
  FROM categories WHERE parentid = 46
  UNION ALL
  SELECT c.id,
         c.name,
         c.parentid,
         c.icon,
         c.url,
         c.position,
         CASE WHEN  c.id not in (select parentid from categories) THEN CONCAT(cte.path)
              ELSE  CONCAT(cte.path,',', c.id) END as path
         ,cte.depth+1
  FROM categories c 
  JOIN cte ON cte.id=c.parentid
)
SELECT id, path FROM cte ORDER BY id;

试试这个[医]小提琴