2
回答
mssql的WITH as转到mysql里面应该怎么写呢,确实头大了
利用AWS快速构建适用于生产的无服务器应用程序,免费试用12个月>>>   
WITH CTE AS ( 
	SELECT C.* FROM ( 
		SELECT DISTINCT ROLE_ID FROM ( 
			SELECT B.ROLE_ID, A.USER_NAME FROM SYS_USER A JOIN SYS_ROLE_USER B ON A.USER_ID = B.USER_ID 
			WHERE A.USER_ID = 'ac4259bc-ce5c-48f4-af2c-18760a26d32b' 
		) A 
	) S JOIN SYS_ROLE_ELEMENT_OPERATE B ON S.ROLE_ID = B.ROLE_ID 
	JOIN SYS_MENU_ITEM C ON B.ELE_ID = C.ELE_ID 
 -- JOIN SYS_APP D ON C.MENU_ID = D.APP_ID 
 JOIN SYS_ROLE F ON S.ROLE_ID = F.ROLE_ID 
 WHERE ((( F.USE_SCOPE = 0) 
    OR ( F.USE_SCOPE = 1 )) 
	 ) OR F.ROLE_ID IS NULL 
 UNION ALL 
 SELECT A.* FROM SYS_MENU_ITEM A JOIN CTE C ON C.P_ITEM_ID = A.ITEM_ID ) 
SELECT DISTINCT E.REF_URL, E.REF_PARAM, CTE.* FROM CTE LEFT JOIN SYS_ELEMENT E ON CTE.ELE_ID = E.ELE_ID 
 WHERE ((( E.USE_SCOPE = 0) 
 OR ( E.USE_SCOPE = 1)) 
 ) OR E.ELE_ID IS NULL ORDER BY CTE.SERIAL_NO

原sql就是这样的,经我改装之后成这样了

SELECT DISTINCT E.REF_URL, E.REF_PARAM, CTE.* FROM (
SELECT C.* FROM(SELECT C.* FROM ( 
		SELECT DISTINCT A.ROLE_ID FROM ( 
			SELECT B.ROLE_ID, A.USER_NAME FROM SYS_USER A JOIN SYS_ROLE_USER B ON A.USER_ID = B.USER_ID 
			WHERE A.USER_ID = 'ac4259bc-ce5c-48f4-af2c-18760a26d32b' 
		) A 
	) S JOIN SYS_ROLE_ELEMENT_OPERATE B ON S.ROLE_ID = B.ROLE_ID 
	JOIN SYS_MENU_ITEM C ON B.ELE_ID = C.ELE_ID 
 JOIN SYS_ROLE F ON S.ROLE_ID = F.ROLE_ID 
 WHERE ((( F.USE_SCOPE = 0) 
    OR ( F.USE_SCOPE = 1 )) 
	 ) OR F.ROLE_ID IS NULL ) c
UNION ALL 	 
SELECT A.* FROM SYS_MENU_ITEM A WHERE  c.P_ITEM_ID = A.ITEM_ID 
) CTE LEFT JOIN SYS_ELEMENT E ON CTE.ELE_ID = E.ELE_ID 
 WHERE (
 (
 ( E.USE_SCOPE = 0) 
  OR ( E.USE_SCOPE = 1)
 ) 
 ) 
 OR E.ELE_ID IS NULL 
 ORDER BY CTE.SERIAL_NO
这里会报错Unknown column 'c.P_ITEM_ID' in 'where clause',确实不知道怎么改了,加班加到现在了,求救!



举报
1的10次方
发帖于3年前 2回/5K+阅
顶部