注册

MySQL递归语句迁移到DM

遥祝 2025/06/30 143 6 已解决

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: V8
【操作系统】:win10
【CPU】: intel
【问题描述】*:在进行数据库切换时遇到递归语句查询,MySQL与DM的表结构和数据一致,查询出来的结果相同但是数据顺序不相同,是我用错方法了嘛?
MySQL DLL: select employee_id, manager_id from ( select t1.employee_id, t1.manager_id, if(find_in_set(manager_id, @pids) > 0, @pids := concat(@pids, ',', employee_id), 0) as ischild from ( select employee_id, manager_id from employees t where 1 = 1 order by manager_id, employee_id ) t1, ( select @pids := 100) t2 ) t3 where ischild != 0

MySQL 结果集:mysql.png

DM DLL-1: SELECT employee_id, manager_id FROM USER_CENTER.EMPLOYEES START WITH manager_id = 100 CONNECT BY NOCYCLE PRIOR employee_id = manager_id ORDER SIBLINGS BY manager_id, employee_id

DM 结果集-1:dm1.png

DM DLL-2:WITH DIRECTREPORTS(employee_id, manager_id) AS (SELECT employee_id, manager_id FROM USER_CENTER.EMPLOYEES WHERE manager_id = 100 UNION ALL SELECT E.employee_id, E.manager_id FROM USER_CENTER.EMPLOYEES E INNER JOIN DIRECTREPORTS D ON E.manager_id = D.employee_id ) SELECT employee_id, manager_id FROM DIRECTREPORTS;

DM 结果集-2:dm2.png

EMPLOYEES 表结构:table.png

在EMPLOYEES表张存在循环依赖的情况,是我使用的方法不对还是语句查询的条件不对,我尝试了两种方法,均未能与原MySQL语句查询出的结果集一致(数据条数一致,顺序不一致),求助各位,不胜感激。

回答 0
暂无回答
扫一扫
联系客服