Skip to end of metadata
Go to start of metadata

Mysql - 用户基本信息

  • 查询所有用户所在组织，部门，岗级，用户组，汇报对象等


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


  • No labels