1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BEGIN
DECLARE childOrgIds VARCHAR(4000);
DECLARE topOrgIds VARCHAR(4000);

SET childOrgIds = '';
SET topOrgIds = '';

SELECT GROUP_CONCAT(kroa.org_id) INTO topOrgIds FROM kk_role_org_authority kroa 
WHERE kroa.org_authority_bh IN(
SELECT kr.org_authority_bh FROM kk_role kr WHERE kr.role_id IN(
SELECT kur.role_id FROM kk_user_role kur WHERE kur.user_id = userId)
);

WHILE topOrgIds IS NOT NULL
DO
SET childOrgIds = CONCAT(childOrgIds, ',', topOrgIds);
SELECT GROUP_CONCAT(org_id) INTO topOrgIds FROM kk_org WHERE FIND_IN_SET(father_id, topOrgIds) > 0;
END WHILE;
RETURN RIGHT(childOrgIds, CHAR_LENGTH(childOrgIds)-1);
END

SELECT kuo.user_id FROM kk_user_org kuo WHERE kuo.org_id IN(getChildOrgIdsByUserId(100))