DB2数据库中的等级查询

659441806

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