需要帮助：如何在没有存储过程的情况下转换Joe Celko SQL IN DB2 [英] NEED HELP: How to convert Joe Celko SQL IN DB2 without Stored Procedure

问题描述

我试图转换我们耗时的递归查询太多了

基于嵌套集模型的有效查询

。

唯一的问题是将邻接列表模型转换为嵌套集合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/1

I am tryieng to convert our time consuming recursive queries too very
efficient queries based
on nested set model.
The only problem is to convert an adjacency list model into a nested set
model, with push down stack algorithm to DB2 query. The client does not want
to use Stored Procedure.
Please any Recurcive or CWE ideas.
Thank''s in advance.

-- Tree holds the adjacency model
CREATE TABLE Tree
(emp CHAR(10) NOT NULL,
boss CHAR(10));

INSERT INTO Tree
SELECT emp, boss FROM Personnel;

-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
emp CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);

BEGIN ATOMIC
DECLARE 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 Stack
SELECT 1, emp, 1, NULL
FROM Tree
WHERE boss IS NULL;

DELETE FROM Tree
WHERE boss IS NULL;

WHILE counter <= (max_counter - 2)
LOOP IF EXISTS (SELECT *
FROM Stack AS S1, Tree AS T1
WHERE S1.emp = T1.boss
AND S1.stack_top = current_top)
THEN
BEGIN -- push when top has subordinates and set lft value
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.emp), counter, NULL
FROM Stack AS S1, Tree AS T1
WHERE S1.emp = T1.boss
AND S1.stack_top = current_top;

DELETE FROM Tree
WHERE emp = (SELECT emp
FROM Stack
WHERE stack_top = current_top + 1);

SET counter = counter + 1;
SET current_top = current_top + 1;
END
ELSE
BEGIN -- pop the stack and set rgt value
UPDATE Stack
SET rgt = counter,
stack_top = -stack_top -- pops the stack
WHERE stack_top = current_top
SET 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

推荐答案

4月11日下午4:29， ; lenygold通过DBMonster.com < u41482 @ uwewrote：

On Apr 11, 4:29 pm, "lenygold via DBMonster.com" <u41482@uwewrote:

我尝试转换我们耗时的递归查询非常好

基于高效的查询

嵌套集模型。

唯一的问题是将邻接列表模型转换为嵌套集合b / b $ b模型，使用下推堆栈算法转换为DB2查询。客户不希望

使用存储过程。

请任何Recurcive或CWE想法。

提前感谢'。 />

I am tryieng to convert our time consuming recursive queries too very
efficient queries based
on nested set model.
The only problem is to convert an adjacency list model into a nested set
model, with push down stack algorithm to DB2 query. The client does not want
to use Stored Procedure.
Please any Recurcive or CWE ideas.
Thank''s in advance.

作为嵌套集的替代方法，您可以在单独的关系中实现

树的闭包。你将获得至少与嵌套集相同的性能提升，并且IMO最终得到一个更容易获得的
掌握模型。请注意，我不是在反对嵌套集，只是

指出了另一种可能性。在我创建了一些

笔记时，我确信这个想法会起作用。马虎，但你可能会对它们感兴趣：

http://fungus.teststation.com/~jon/t...eeHandling.htm

- Tree持有邻接模型

CREATE TABLE树

（emp CHAR（10）NOT NULL PRIMARY KEY，

boss CHAR（ 10））;

-- Tree holds the adjacency model
CREATE TABLE Tree
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10));

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

- < br $>
- 创建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_EMP
AFTER INSERT ON TREE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN (N.boss IS NOT NULL)
BEGIN ATOMIC
insert into TreeClosure (emp, boss)
values (N.emp, N.boss)
union all
select 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 ~]

db2" insert into tree（emp）values''A''"

DB20000I SQL命令成功完成。

[ lelle @ 53dbd181~]

db2 "insert into tree (emp) values ''A''"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 ~]

db2"插入树（emp，boss）值

（''B''，''A''）" ;

DB20000I SQL命令成功完成。

[lelle @ 53dbd181~]

db2 "insert into tree (emp, boss) values
(''B'',''A'')"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 ~]

这篇关于需要帮助：如何在没有存储过程的情况下转换Joe Celko SQL IN DB2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！