需要帮助：如何在没有存储过程的情况下转换Joe Celko SQL IN DB2

本文介绍了需要帮助：如何在没有存储过程的情况下转换Joe Celko SQL IN DB2的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我试图转换我们耗时的递归查询太多了 基于嵌套集模型的有效查询 。 唯一的问题是将邻接列表模型转换为嵌套集合b / b $ b模型，使用下推堆栈算法到DB2查询。客户不希望 使用存储过程。 请任何Recurcive或CWE想法。 提前感谢'。 /> - Tree持有邻接模型 CREATE TABLE树 （emp CHAR（10）NOT NULL， boss CHAR（10））; INSERT INTO树 SELECT emp，老板来自人员; - 堆栈开始为空，将保持嵌套集模型 CREATE TABLE堆栈 （stack_top INTEGER NOT NULL， emp CHAR（ 10）NOT NULL， lft INTEGER， rgt INTEGER）; BEGIN ATOMIC DECLARE计数器INTEGER; DECLARE max_counter INTEGER; DECLARE current_top INTEGER; SET counter = 2; SET max_counter = 2 *（SELECT COUNT（*）FROM Tree）; SET current_top = 1; INSERT INTO Stack SELECT 1，emp，1，NULL 来自树 WHERE老板IS NULL; 从树上删除 WHERE boss为NULL; WHILE counter< =（max_counter - 2） LOOP IF EXISTS（选择* 来自Stack AS S1，Tree AS T1 WHERE S1.emp = T1.boss AND S1 .stack_top = current_top） 那么 BEGIN - 当top有下属并设置lft值时推送 INSERT INTO Stack SELECT（current_top + 1），MIN（T1.emp），计数器，NULL 来自堆栈AS S1，树AS T1 WHERE S1.emp = T1 .boss AND S1.stack_top = current_top; 从树上删除 WHERE emp =（SELECT emp 来自Stack WHERE stack_top = current_top + 1）; SET counter = counter + 1; SET current_top = current_top + 1; END ELSE BEGIN - 弹出堆栈并设置rgt值 UPDATE Stack SET rgt = counter， stack_top = -stack_top - 弹出堆栈 WHERE st ack_top = current_top SET counter = counter + 1; SET current_top = current_top - 1; END IF; END LOOP; END; - 通过DBMonster.com发布消息 http://www.dbmonster.com/Uwe/Forums .... m-db2 / 200804/1I am tryieng to convert our time consuming recursive queries too veryefficient queries basedon nested set model.The only problem is to convert an adjacency list model into a nested setmodel, with push down stack algorithm to DB2 query. The client does not wantto use Stored Procedure.Please any Recurcive or CWE ideas.Thank''s in advance.-- Tree holds the adjacency modelCREATE TABLE Tree(emp CHAR(10) NOT NULL,boss CHAR(10));INSERT INTO TreeSELECT emp, boss FROM Personnel;-- Stack starts empty, will holds the nested set modelCREATE TABLE Stack(stack_top INTEGER NOT NULL,emp CHAR(10) NOT NULL,lft INTEGER,rgt INTEGER);BEGIN ATOMICDECLARE counter INTEGER;DECLARE max_counter INTEGER;DECLARE current_top INTEGER;SET counter = 2;SET max_counter = 2 * (SELECT COUNT(*) FROM Tree);SET current_top = 1;INSERT INTO StackSELECT 1, emp, 1, NULLFROM TreeWHERE boss IS NULL;DELETE FROM TreeWHERE boss IS NULL;WHILE counter <= (max_counter - 2)LOOP IF EXISTS (SELECT *FROM Stack AS S1, Tree AS T1WHERE S1.emp = T1.bossAND S1.stack_top = current_top)THENBEGIN -- push when top has subordinates and set lft valueINSERT INTO StackSELECT (current_top + 1), MIN(T1.emp), counter, NULLFROM Stack AS S1, Tree AS T1WHERE S1.emp = T1.bossAND S1.stack_top = current_top;DELETE FROM TreeWHERE emp = (SELECT empFROM StackWHERE stack_top = current_top + 1);SET counter = counter + 1;SET current_top = current_top + 1;ENDELSEBEGIN -- pop the stack and set rgt valueUPDATE StackSET rgt = counter,stack_top = -stack_top -- pops the stackWHERE stack_top = current_topSET counter = counter + 1;SET current_top = current_top - 1;END IF;END LOOP;END;--Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums....m-db2/200804/1推荐答案 作为嵌套集的替代方法，您可以在单独的关系中实现 树的闭包。你将获得至少与嵌套集相同的性能提升，并且IMO最终得到一个更容易获得的掌握模型。请注意，我不是在反对嵌套集，只是 指出了另一种可能性。在我创建了一些 笔记时，我确信这个想法会起作用。马虎，但你可能会对它们感兴趣： http://fungus.teststation.com/~jon/t...eeHandling.htm CREATE TABLE TreeClosure （emp CHAR（10）NOT NULL， boss CHAR （10）NOT NULL， PRIMARY KEY（emp，boss））; CREATE TRIGGER ADD_EMP 插入树后 引用新的N 每行 模式DB2SQL WHEN（N.boss IS NOT NULL） BEGIN ATOMIC 插入TreeClosure（emp，boss） 值（N.emp，N.boss） union all 从TreeClosure X中选择N.emp，X.boss，其中X.emp = N.boss; - 结束; - del和move的执行触发器，见上面的链接 - CREATE TRIGGER DEL_EMP - - 创建TRIGGER MOVE_EMP - 添加一些示例数据 [lelle @ 53dbd181~]CREATE TABLE TreeClosure(emp CHAR(10) NOT NULL,boss CHAR(10) NOT NULL,PRIMARY KEY (emp, boss));CREATE TRIGGER ADD_EMPAFTER INSERT ON TREEREFERENCING NEW AS NFOR EACH ROWMODE DB2SQLWHEN (N.boss IS NOT NULL)BEGIN ATOMICinsert into TreeClosure (emp, boss)values (N.emp, N.boss)union allselect N.emp, X.boss from TreeClosure X where X.emp = N.boss;--END;-- implent triggers for del and move, see link above-- CREATE TRIGGER DEL_EMP----CREATE TRIGGER MOVE_EMP--Add some sample data[lelle@53dbd181 ~] 这篇关于需要帮助：如何在没有存储过程的情况下转换Joe Celko SQL IN DB2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！