为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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 结果集:
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:
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:
EMPLOYEES 表结构:
在EMPLOYEES表张存在循环依赖的情况,是我使用的方法不对还是语句查询的条件不对,我尝试了两种方法,均未能与原MySQL语句查询出的结果集一致(数据条数一致,顺序不一致),求助各位,不胜感激。
看你Mysql输出结果里,还是以 manager_id,employee_id 顺序排序,并未考虑层次关系,那你在使用 CONNECT BY方式递归查询的排序里,去掉 SIBLINGS 关键字,直接用 ORDER BY manager_id, employee_id 方式,看看结果是否与mysql一致了