我想在mysql中使用递归代码,但服务器不支持它
所以请给我一些建议
这是我的递归查询
mysql(Recursive)
WITH RECURSIVE CTE AS
(SELECT ssn, superssn, 0 as depth from
EMPLOYEE where ssn='888665555'
UNION ALL
SELECT A.SSN, A.SUPERSSN, depth + 1 FROM
EMPLOYEE A INNER JOIN CTE WHERE A.SUPERSSN
= CTE.SSN )
SELECT * FROM CTE;
i try this
mysql> select ssn,superssn
from(select * from EMPLOYEE order by superssn,ssn) EMPLOYEE_sorted,
(select @pv := '888665555') initialisation where find_in_set(superssn, @pv)
and length(@pv := concat(@pv,',',ssn));
the result
+-----------+-----------+
| ssn | superssn |
+-----------+-----------+
| 333445555 | 888665555 |
| 987654321 | 888665555 |
| 987987987 | 987654321 |
| 999887777 | 987654321 |
| 000000001 | 999887777 |
+-----------+-----------+
but the correct result is
+-----------+-----------+
| ssn | superssn |
+-----------+-----------+
| 000000001 | 999887777 |
| 000000002 | 000000001 |
| 000000003 | 000000002 |
| 000000004 | 000000003 |
| 000000005 | 000000004 |
| 000000006 | 000000005 |
| 123456789 | 333445555 |
| 333445555 | 888665555 |
| 453453453 | 333445555 |
| 666884444 | 333445555 |
| 987654321 | 888665555 |
| 987987987 | 987654321 |
| 999887777 | 987654321 |
+-----------+-----------+
data table 从EMPLOYEE中选择ssn,superssn;
+-----------+-----------+
| ssn | superssn |
+-----------+-----------+
| 888665555 | NULL |
| 000000002 | 000000001 |
| 000000003 | 000000002 |
| 000000004 | 000000003 |
| 000000005 | 000000004 |
| 000000006 | 000000005 |
| 123456789 | 333445555 |
| 453453453 | 333445555 |
| 666884444 | 333445555 |
| 333445555 | 888665555 |
| 987654321 | 888665555 |
| 987987987 | 987654321 |
| 999887777 | 987654321 |
| 000000001 | 999887777 |
+-----------+-----------+
这是数据表你可以使用参考可以有人给出建议我怎么能改变它?!谢谢