SELECT c.*, d .Organization_Name, d .ParentId, e.Roles_ID, e.Roles_Name
FROM
(
SELECT a.*, b.Organization_ID
FROM
(
SELECT a.User_ID, MIN(User_Code) User_Code, MIN(User_Account) User_Account,
MIN(User_Pwd) User_Pwd, MIN(User_Name) User_Name, MIN(User_Sex) User_Sex,
min(User_IDcardNo) User_IDcardNo, MIN(Mobile) Mobile, MIN(DeleteMark) DeleteMark,
ztbm_id_s = STUFF (
(SELECT ',' + b.ZTBM_ID FROM TB_ZYM_UserZTBM b WHERE b.User_ID = a.User_ID FOR XML PATH('')),
1, 1, ''
)
FROM Base_UserInfo a
GROUP BY a.user_id
) a
LEFT OUTER JOIN dbo.Base_StaffOrganize b ON a.User_ID = b.User_ID) c
LEFT OUTER JOIN dbo.Base_Organization d ON c.Organization_ID = d .Organization_ID
LEFT OUTER JOIN (
SELECT b.*, a.User_ID FROM dbo.Base_UserRole a
INNER JOIN dbo.Base_Roles b ON a.Roles_ID = b.Roles_ID
)
e ON e.User_ID = c.User_ID
SELECT c.*, d .Organization_Name, d .ParentId, e.Roles_ID, e.Roles_Name
FROM
(
SELECT a.*, b.Organization_ID
FROM
(
SELECT a.User_ID, MIN(User_Code) User_Code, MIN(User_Account) User_Account,
MIN(User_Pwd) User_Pwd, MIN(User_Name) User_Name, MIN(User_Sex) User_Sex, min(User_IDcardNo)
User_IDcardNo, MIN(Mobile) Mobile, MIN(DeleteMark) DeleteMark
,(select wm_concat(b.ZTBM_ID) from TB_ZYM_UserZTBM b WHERE b.User_ID = a.User_ID ) ztbm_id_s
FROM Base_UserInfo a
GROUP BY a.user_id
) a
LEFT OUTER JOIN Base_StaffOrganize b ON a.User_ID = b.User_ID
) c
LEFT OUTER JOIN Base_Organization d ON c.Organization_ID = d .Organization_ID
LEFT OUTER JOIN (
SELECT b.*, a.User_ID FROM Base_UserRole a
INNER JOIN Base_Roles b ON a.Roles_ID = b.Roles_ID
)
e ON e.User_ID = c.User_ID
,wm_concat(pic_url) pic_url
,pic_url=STUFF((select '|'+pic_url from TB_ZYM_LOG_Image u where u.log_id=TB_ZYM_LOG_Image.log_id FOR xml path('')),1,1,'')
1查询所有@ 替换成：
2 SqlDatabase-->OracleDatabase
3 top
4 STUFF--->wm_concat
5 dbo. 去除
6 as 去除
7 case when
8 with xxx as
9 cast
10 isnull---》nvl
11 GETDATE() ---》 sysdate
12 NEWID()-----SYS_GUID()
13 null和’’（空字符串）是一个意思 https://www.cnblogs.com/memory4young/p/use-null-empty-space-in-oracle.html
14 oracle 没有 [字段] 这种表达方式 去掉 中括号