我有以下表格,该groups表格包含按层次排列的组,group_member并存储用户所属的组。
id
parent_id name
id group_id user_id
1 NULL Cerebra 2 1 CATS 3 2 CATS 2.0 4 1 Cerepedia 5 4 Cerepedia 2.0 6 1 CMS
1 1 3 2 1 4 3 1 5 4 2 7 5 2 6 6 4 6 7 5 12 8 4 9 9 1 10
我想检索给定用户的可见组。就是说用户所属的组以及这些组的子级。例如,使用以上数据:
USER VISIBLE_GROUPS 9 4, 5 3 1,2,4,5,6 12 5
我正在使用递归和几个数据库查询来获取这些值。但是我想知道是否可以通过单个SQL查询来提高我的应用程序性能。我正在使用MySQL。
问题来源于stack overflow
我想到两件事:
1-您可以反复将表外部连接到自身,以递归地沿树走,如:
SELECT * FROM MY_GROUPS MG1 ,MY_GROUPS MG2 ,MY_GROUPS MG3 ,MY_GROUPS MG4 ,MY_GROUPS MG5 ,MY_GROUP_MEMBERS MGM WHERE MG1.PARENT_ID = MG2.UNIQID (+) AND MG1.UNIQID = MGM.GROUP_ID (+) AND MG2.PARENT_ID = MG3.UNIQID (+) AND MG3.PARENT_ID = MG4.UNIQID (+) AND MG4.PARENT_ID = MG5.UNIQID (+) AND MGM.USER_ID = 9
那会给你这样的结果:
UNIQID PARENT_ID NAME UNIQID_1 PARENT_ID_1 NAME_1 UNIQID_2 PARENT_ID_2 NAME_2 UNIQID_3 PARENT_ID_3 NAME_3 UNIQID_4 PARENT_ID_4 NAME_4 UNIQID_5 GROUP_ID USER_ID 4 2 Cerepedia 2 1 CATS 1 null Cerebra null null null null null null 8 4 9
此处的限制是,您必须为要沿树移动的每个“级别”添加新的联接。如果您的树少于20个级别,则可以通过创建一个显示每个用户20个级别的视图来摆脱它。
2-我知道的唯一其他方法是创建一个递归数据库函数,然后从代码中调用它。这样,您仍然会有一些查找开销(即,您的查询数量仍将等于您在树上行走的级别数量),但总体而言,它应该更快,因为它们全部在数据库中进行。
我不确定MySql,但是在Oracle中,此功能类似于此功能(您必须更改表名和字段名;我只是复制过去所做的事情):
CREATE OR REPLACE FUNCTION GoUpLevel(WO_ID INTEGER, UPLEVEL INTEGER) RETURN INTEGER IS BEGIN DECLARE iResult INTEGER; iParent INTEGER; BEGIN IF UPLEVEL <= 0 THEN iResult := WO_ID; ELSE SELECT PARENT_ID INTO iParent FROM WOTREE WHERE ID = WO_ID;
iResult := GoUpLevel(iParent,UPLEVEL-1); --recursive END; RETURN iResult; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; END GoUpLevel; /
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。