达梦数据库层次查询ORDER SIBLINGS BY踩坑

版本：DM Database Server 64 V8

背景：达梦数据适配，发现菜单层级数据查询时，ORDER SIBLINGS BY与IN联用时，会影响层内顺序

原SQL在Oracle数据库中可以正常查询，在达梦数据库中查询层级关系错乱

原SQL：

SELECT
	MENUID,
	MENUCAPTION 
FROM
	Jill_menu 
WHERE
	MENUID IN ( SELECT B.PROGID FROM JILL_ROLE B WHERE B.ROLEID IN ( 'admin' ) ) 
	AND PROGRAMID = 'XX' START WITH MENUID = 'XX' CONNECT BY PRIOR MENUID = MENUPARENTID ORDER siblings BY PHARMACYMENUORDER ASC nulls last

修改查询范围

修改后SQL：

SELECT
	MENUID,
	MENUCAPTION
FROM
	( SELECT * FROM Jill_menu WHERE MENUID IN ( SELECT B.PROGID FROM JILL_ROLE B WHERE B.ROLEID IN ( 'admin' ) ) ) 
WHERE
	 
	PROGRAMID = 'xx' START WITH MENUID = 'xx' CONNECT BY PRIOR MENUID = MENUPARENTID ORDER siblings BY PHARMACYMENUORDER ASC nulls last
	

没找到具体原因，先记录下来，后续处理