我有这个父表。我的目标是找出给定id的所有后代。
例如,对于以下表格:
给定父级为0,我想要得到:
我的限制/注意事项: 1. 最坏情况下我可以有4个层次结构。 2. 我的数据库是MYSQL(这意味着我无法编写递归查询)。 3. 表格id_to_id相当小..最多只有100行。
我考虑的解决方案类似于以下SQL查询:
但是那个内部查询会被执行4次(我理解错了吗?):
所以我的问题是:
+----------+-----+
| parentId | id |
+----------+-----+
| 0 | 1 |
| 0 | 2 |
| 0 | 3 |
| 0 | 4 |
| 1 | 5 |
| 1 | 11 |
| 5 | 12 |
| 12 | 13 |
| 14 | 15 |
| 19 | 20 |
| 20 | 24 |
+----------+-----+
给定父级为0,我想要得到:
+----+
| Id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 11 |
| 12 |
| 13 |
+----+
我的限制/注意事项: 1. 最坏情况下我可以有4个层次结构。 2. 我的数据库是MYSQL(这意味着我无法编写递归查询)。 3. 表格id_to_id相当小..最多只有100行。
我考虑的解决方案类似于以下SQL查询:
SELECT DISTINCT(T.Id)
FROM(
SELECT t1.Id
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0
UNION ALL
SELECT t2.Id as lev2
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0
UNION ALL
SELECT t3.Id as lev3
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0
UNION ALL
SELECT t4.Id as lev4
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0) as T
WHERE T.Id IS NOT NULL;
但是那个内部查询会被执行4次(我理解错了吗?):
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0) as T
所以我的问题是:
- 有没有想法可以让它在不连接4次的情况下工作?或者对于该查询的另一种聪明解决方案?
- 如何编写查询,可以在表上执行相同的操作(我可以这样做吗?)而无需给定参数 - 类似于:
+----+------------+
| Id | decedents |
+----+------------+
| 0 | 1,2,3,4,...|
| 1 | 5,11,... |
+----+------------+
谢谢,Ido