我有一张 table (users_groups
):
+-----------+------------+---------+
| groupGUID | memberGUID | isGroup |
+-----------+------------+---------+
| 32AB160C | 5B277276 | 0 |
| 32AB160C | 0A023D1D | 0 |
| 5C952B2E | 32AB160C | 1 |
| 4444FTG5 | 5C952B2E | 1 |
+-----------+------------+---------+
isGroup
列指示memberGUID
是否为组。我想获得一个新表(
new_users_groups
),所有组成员身份都已解决:+-----------+------------+
| groupGUID | memberGUID |
+-----------+------------+
| 32AB160C | 5B277276 |
| 32AB160C | 0A023D1D |
| 5C952B2E | 5B277276 |
| 5C952B2E | 0A023D1D |
| 4444FTG5 | 5B277276 |
| 4444FTG5 | 0A023D1D |
+-----------+------------+
现在,我正在手动做所有事情:
SELECT * FROM users_groups WHERE isGroup = 1;
SELECT * FROM users_groups WHERE groupGUID = '5C952B2E'
INSERT INTO new_users_groups (groupGUID, memberGUID) VALUES ('5C952B2E', '5B277276'); INSERT INTO new_users_groups (groupGUID, memberGUID) VALUES ('5C952B2E', '0A023D1D');
我该如何自动化呢?也许使用递归CTE?
最佳答案
您可以使用递归CTE进行此操作:
with cte as (
select ug.groupGUID, ug.groupGUID as grp, ug.memberGUID
from user_groups ug
where isGroup = 0
union all
select ug.groupGUID, ug.groupGUID as grp, cte.memberGUID
from user_groups ug join
cte
on cte.grp = ug.memberGUID
)
select groupGUID, memberGUID
from cte;
这是一个Rextester。