DB2中如何实现等级查询

在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; 在oracle数据库中,很容易用connect by prior… start with…语句