DB2数据库中的等级查询

1. 现象、问题描述
在实际的工作中,会有大量的上下级关系.如经理和职员,父目录和子目录等.在管理系统中,会将这样的关系存放在一张数据表中,因为经理和职员,父目录和子目录都有同样的数据属性..
经常会有这样的查询需求,如将经理A的所有职员信息和经理的编号,名称一起查询出来.用普通的SQL就搞不定了.
MDSP项目组,原来都是用oracle数据库,但在V3D5中,开始用DB2数据库.MDSP的MDMC模块,需要将一棵树型的目录结构,在IE中展现出来.
2. 关键过程、根本原因分析
在oracle数据库中,很容易用connect by prior… start with…语句来搞定.但在DB2数据库中,就很麻烦,需要自己写函数来实现这个功能.
目录的数据结构如下:
Folder(folderID,parentID,Name,status)
根目录的ID为ROOT,父ID为null.
在oracle中,将这颗树查询出来的sql语句如下:
Select folderid, parented,name from folder f connect by prior folderid=parentid start with folderID=’ROOT’;
在DB2数据库中,数据库函数支持递归SQL语句.
先定义一下函数,用来返回某个目录的所有子目录.
CREATE FUNCTION F_GETDIRECTCHILDS (PARENT VARCHAR(20))
RETURNS TABLE (ID VARCHAR(20))
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
RETURN
SELECT child_id
FROM ( SELECT folderid FROM folder WHERE folder.parentid = f_getdirectchilds.parent) AS T(child_id);
再定义一个函数,用于外部程序调用.来生成所需要的树型结构.
CREATE FUNCTION F_GETRECCHILDS (ROOT VARCHAR(20))
RETURNS TABLE (ID VARCHAR(20))
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
RETURN
WITH rec(id) AS
(
values(root)
UNION ALL SELECT t.id FROM rec,
TABLE(f_getdirectchilds(rec.id)) AS T
)
SELECT id FROM rec;
用下面的语句,就可以达到与oracle数据库中同样的效果.
SELECT T.id folderid, parentid ,name FROM TABLE(F_GETRECCHILDS('ROOT')) AS T, folder a where T.id = a.folderid;

3. 结论、解决方案及效果
对于很特殊的查询要求,要充分利用所用数据库的特性,将棘手的问题化解掉.