mssql的WITH as转到mysql里面应该怎么写呢,确实头大了

1的10次方 发布于 2015/03/17 20:37
阅读 8K+
收藏 0
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',确实不知道怎么改了,加班加到现在了,求救!



加载中
0
CapJes
CapJes
回家睡一觉,当你醒来时,说不定这个问题迎刃而解。有时候太专注反而不容易找不到答案。
1的10次方
1的10次方
我已经睡了一觉又来了!
0
白与黑
白与黑
SELECT A.*FROM SYS_MENU_ITEM AWHERE  c.P_ITEM_ID = A.ITEM_ID   这个sql里没有对c的定义  前边的c是在union之前  后边的sql是读不到的     可能是这样   本人能力有限
1的10次方
1的10次方
嗯,估计就是union这里有问题
返回顶部
顶部