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;