SELECT
u.username,
u.firstName,
u.lastName,
u.email,
u.active,
e.employeeCode,
e.role,
e.gradeId,
e.gradeName,
e.departmentId,
e.departmentName,
e.organizationId,
e.orgName,
e.reportToUsername,
e.reportToName,
ug.groups
FROM dir_user u
-- 关联组织部门信息
LEFT JOIN (SELECT
e.userId,
e.employeeCode,
e.role,
e.gradeId,
g.name gradeName,
e.departmentId,
d.name departmentName,
e.organizationId,
o.name orgName,
rp.reportToUsername,
rp.reportToName
FROM dir_employment e
LEFT JOIN dir_organization o ON o.id=e.organizationId
LEFT JOIN dir_department d ON d.id=e.departmentId
LEFT JOIN dir_grade g ON g.id=e.gradeId
-- 子查询关联汇报对象
LEFT JOIN (SELECT
r.employmentId,
e.userId reportToUsername,
CONCAT(u.firstName,' ',u.lastName) reportToName
FROM dir_employment_report_to r
INNER JOIN dir_employment e ON e.id=r.reportToId
INNER JOIN dir_user u ON e.userId=u.username) rp ON rp.employmentId=e.id
) e ON e.userId=u.username
-- 关联用户组信息
LEFT JOIN (SELECT
ug.userId,
GROUP_CONCAT(g.name SEPARATOR ',\n') groups
FROM dir_user_group ug
INNER JOIN dir_group g ON g.id=ug.groupId
GROUP BY ug.userId) ug ON ug.userId=u.username