Go

CREATE VIEW [dbo].[ZL_User_PlatView]

AS

SELECT a_1.*,b.CompName

,Cast(','+(SELECT GroupName+',' FROM ZL_Plat_Group  Where MemberIDS Like '%,'+ CAST(UserID as varchar(20))+',%' FOR XML PATH(''))as varchar(300)) as GroupName

,Cast(','+(SELECT CAST(ID as varchar(20))+',' FROM ZL_Plat_Group  Where MemberIDS Like '%,'+ CAST(UserID as varchar(20))+',%' FOR XML PATH(''))as varchar(300)) as Gid

FROM  (SELECT u.*, up.CompID,up.Plat_Group,up.Plat_Role,up.Status,up.CreateTime,up.ATCount

FROM  (SELECT A.UserID,A.UserName,A.Status AS UserStatus,A.HoneyName AS TRUENAME,A.HoneyName,A.salt AS UserFace,B.Position,b.Position as Post, B.Mobile FROM ZL_User A LEFT JOIN ZL_UserBase B ON A.UserID=B.UserID ) AS u INNER JOIN

ZL_User_Plat AS up ON u.UserID = up.UserID) AS a_1 LEFT OUTER JOIN

ZL_Plat_Comp AS b ON a_1.CompID = b.ID