查询机构部门用户树

首先说说我们的数据结构：机构表sys_organization(id,parent_organ,organ_simple_name,orderby)
,部门表sys_department(id,parent_department,sys_organization,department_name,orderby)
(因为部门是挂在机构下面的，对于上级部门即parent_department为空的那些部门则是直接挂在机构下面，
而parent_department不为空的话则是挂在部门下面),用户表sysuser(id,sys_department,user_name,orderby)
(对于用户则是直接挂在部门下面的)


select rs.*,level from (
select 'org'||org.id id,'org'||org.parent_organ pid,org.organ_simple_name showname,org.orderby orderby from sys_organization org where org.is_valid=1
union all
select 'dep'||dep.id id,'org'||dep.sys_organization pid,dep.department_name showname,dep.orderby orderby from sys_department dep where dep.is_valid=1 and dep.parent_department is null
union all
select 'dep'||dep.id id,'dep'||dep.parent_department pid,dep.department_name showname,dep.orderby orderby from sys_department dep where dep.is_valid=1 and dep.parent_department is not null
union all
select 'usr'||su.id id,'dep'||su.sys_department pid,su.user_name showname,su.orderby orderby from sysuser su where su.is_valid=1
)rs connect by rs.pid=prior rs.id start with rs.id='org1' order siblings by rs.orderby


这样就可以查出按机构部门用户构成树，同时内部使用orderby排序顺序的一个查询结果

以上内容抄自原来公司陈总，链接http://hi.baidu.com/czy19880623/item/1be92e477e3b3e2410ee1e35

这是oracle中的用法，mysql中好像没有，不过简单的mysql可以拼出来