select top 1关联查父级名称
SELECT users.UserID AS Id, users.UserName, users.TrueName, users.Phone, users.Email, STUFF( ( SELECT ',' + roles.Description FROM Accounts_UserRoles userroles LEFT JOIN Accounts_Roles roles ON roles.RoleID = userroles.RoleID WHERE userroles.UserID = users.UserID FOR XML PATH('') ), 1, 1, '' ) AS RoleNames, ( SELECT TOP 1 UserName FROM Accounts_Users sup WHERE sup.UserID = users.SuperiorId AND users.SuperiorId IS NOT NULL ) AS SuperiorName, users.Activity, users.IsSuperior, users.DataRange, dept.DeptName FROM Accounts_Users users LEFT JOIN DeptInfo dept ON dept.DeptId = users.DeptID AND dept.DelFlag = 0 WHERE users.IsDelete = 0 AND users.ClientCode IS NULL;
此随笔或为自己所写、或为转载于网络。仅用于个人收集及备忘。