表:
人员表:t_sys_user
组织机构表:t_sys_organization
组织机构人员关系表:t_sys_user_organization
需求:传入一个组织机构id查询该机构下面所有的人员
SQL:
with recursive t as(
SELECT * FROM t_sys_organization WHERE organization_id='150030595752464384'
UNION ALL
SELECT c.* FROM t_sys_organization c JOIN t ON c.parent_id = t.organization_id
)
SELECT user.user_id
FROM t_sys_user user
JOIN t_sys_user_organization uo on user.user_id = uo.user_id
JOIN t on t.organization_id = uo.organization_id
结果: