使用表的自连接来实现层次化查询

对于层次化的查询，在ORACLE中使用CONNECT BY是很方便的。可惜并不是任何时候都有ORACLE可以使用，在其他环境下就得使用一些其他办法了。今天就碰上这么个问题，在POSTGRESQL里不知道有没有类似的语法，刚刚接触没几天，还不太熟悉，就用表的自连接来实现一个类似功能吧。

 

--测试数据 SQL> create table dept_t(deptno number,deptname varchar2(20),mgrno number); Table created. SQL> insert into dept_t values(1,'总公司',null); 1 row created. SQL> insert into dept_t values(2,'浙江分公司',1); 1 row created. SQL> insert into dept_t values(3,'杭州分公司',2); 1 row created. SQL> commit; Commit complete. --下面这个是在ORACLE中的写法 SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept_t connect by prior deptno=mgrno; MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2)) -------------------------------------------------------------------------------- 总公司,浙江分公司,杭州分公司 --这里MAX的作用是从多条记录中取出上面想要的那条，下面去掉MAX看看 SQL> select substr(sys_connect_by_path(deptname,','),2) from dept_t connect by p rior deptno=mgrno; SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2) -------------------------------------------------------------------------------- 浙江分公司 浙江分公司,杭州分公司 杭州分公司 总公司 总公司,浙江分公司 总公司,浙江分公司,杭州分公司 6 rows selected. --用表的自连接来试试 SQL> select t1.deptname||','||t2.deptname||','||t3.deptname 2 from dept_t t1,dept_t t2,dept_t t3 3 where t3.deptno = t2.mgrno 4 and t2.deptno = t1.mgrno; T1.DEPTNAME||','||T2.DEPTNAME||','||T3.DEPTNAME -------------------------------------------------------------- 杭州分公司,浙江分公司,总公司 SQL> SQL> select t1.deptname||','||t2.deptname --这里关联两个表，就只取出了有两层关系的记录，上面三个表关联同理 2 from dept_t t1,dept_t t2 3 where t2.deptno = t1.mgrno; T1.DEPTNAME||','||T2.DEPTNAME ----------------------------------------- 浙江分公司,总公司 杭州分公司,浙江分公司 SQL> select t1.deptname 2 from dept_t t1; DEPTNAME -------------------- 总公司 浙江分公司 杭州分公司 SQL> select t1.deptname||','||t2.deptname||','||t3.deptname 2 from dept_t t1,dept_t t2,dept_t t3 3 where t3.deptno = t2.mgrno 4 and t2.deptno = t1.mgrno 5 union all 6 select t1.deptname||','||t2.deptname 7 from dept_t t1,dept_t t2 8 where t2.deptno = t1.mgrno 9 union all 10 select t1.deptname 11 from dept_t t1; T1.DEPTNAME||','||T2.DEPTNAME||','||T3.DEPTNAME -------------------------------------------------------------- 杭州分公司,浙江分公司,总公司 浙江分公司,总公司 杭州分公司,浙江分公司 总公司 浙江分公司 杭州分公司 6 rows selected. SQL> select max(tree) 2 from 3 ( 4 select t1.deptname||','||t2.deptname||','||t3.deptname as tree 5 from dept_t t1,dept_t t2,dept_t t3 6 where t3.deptno = t2.mgrno 7 and t2.deptno = t1.mgrno 8 union all 9 select t1.deptname||','||t2.deptname 10 from dept_t t1,dept_t t2 11 where t2.deptno = t1.mgrno 12 union all 13 select t1.deptname 14 from dept_t t1 15 ) a; MAX(TREE) -------------------------------------------------------------- 总公司 --出错了，因为这里把总公司放在了后面，MAX函数判断的时候就会把"总公司"这条记录取出来。下面把父节点放在前面试试 SQL> select max(tree) 2 from 3 ( 4 select t3.deptname||','||t2.deptname||','||t1.deptname as tree 5 from dept_t t1,dept_t t2,dept_t t3 6 where t3.deptno = t2.mgrno 7 and t2.deptno = t1.mgrno 8 union all 9 select t2.deptname||','||t1.deptname 10 from dept_t t1,dept_t t2 11 where t2.deptno = t1.mgrno 12 union all 13 select t1.deptname 14 from dept_t t1 15 ) a; MAX(TREE) -------------------------------------------------------------- 总公司,浙江分公司,杭州分公司