如何使用SQL获取整个链接组详细信息？
在数据库中有称为“MYGROUP”的表。 我在GUI中以树形格式显示这个表格数据如下：
Vishal Group
|
|-------Vishal Group1
| |-------Vishal Group1.1
| |-------Vishal Group1.1.1
|
|-------Vishal Group2
| |-------Vishal Group2.1
| |-------Vishal Group2.1.1
|
|-------Vishal Group3
|
|-------Vishal Group4
| |-------Vishal Group4.1
实际上，要求是，我需要访问每个组的最低根目录，如果相应的组未在其他特定的表中使用，那么我将从相应的表中删除该记录。
我只需要获取名为“Vishal Group”的主要组的所有细节，请参阅两个捕捉，一个包含整个表格数据，另一个捕捉（捕捉具有树形细节的细节）显示预期数据，即我只需要获取这些记录是SQL执行的结果。
我尝试了自我加入（通常我们为MGR和员工列关系做），但没有成功获取属于所有记录基础的“Vishal Group”下的记录。
我已经添加了一个表DDL并插入了SQL以供参考，如下所示。 并附上了表格中的数据外观。
CREATE TABLE MYGROUP
(
PK_GROUP GUID DEFAULT 'newid()' NOT NULL,
DESCRIPTION Varchar(255),
LINKED_TO_GROUP GUID,
PRIMARY KEY (PK_GROUP)
);
COMMIT;
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{11111111-111-1111-1111-111111111111} ', 'My Items', NULL);
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}', 'Vishal Group', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}', 'Vishal Group1', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{A87E921D-0468-497D-92C5-19AB63751EE8}', 'Vishal Group1.1', '{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{0FDC729A-8FCC-4D23-8619-436A459835DD}', 'Vishal Group1.1.1', '{A87E921D-0468-497D-92C5-19AB63751EE8}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}', 'Vishal Group2', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}', 'Vishal Group2.1', '{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{A326E6E3-030E-493B-AA0E-DC5D90DB080F}', 'Vishal Group2.1.1', '{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{3CF1FE37-EEC0-4E79-A3C5-DB78F6A9BC05}', 'Vishal Group3', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}', 'Vishal Group4', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{2EB81764-04FA-4DDA-9AAB-A607BDC2756D}', 'Vishal Group4.1', '{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{7D939081-13F0-404C-9F2F-5222C628FDCC}', 'Sample BOMs', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{C77D2255-AC47-461D-BEE5-7F3154C23AF1}', 'Test1', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{D054539A-BBBA-4E3F-9746-1522FF8A1E89}', 'Test2', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{71B4751C-7096-4FB9-8D71-6BB19A3D9ED9}', 'Trailer Assy', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{F702BABB-73B0-4A49-B442-1C7C8A126335}', 'WIP', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{FC74D59A-94E3-4209-BCEA-1B7606EA62F1}', 'mmmmmm', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{6E4354F9-B298-4737-9C18-51B4ACAC0734}', 'test1', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{42A48EE0-D4EE-4828-BC11-D7F0D1FE5BEC}', 'test1', '{11111111-111-1111-1111-111111111111}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{28AFE8E1-1221-4F94-BAE3-37EA6B360494}', 'test_2', '{11111111-111-1111-1111-111111111111}');
COMMIT;
任何想法如何获得“维沙尔集团”下的记录？
你可以像这样使用递归存储过程：
SET TERM ^ ;
create or alter procedure MYGROUP_PROC (
IPARENT varchar(64))
returns (
PK_GROUP varchar(64),
DESCRIPTION varchar(255),
LINKED_TO_GROUP varchar(64))
as
declare variable I integer;
BEGIN
FOR
select
mygroup.pk_group,
mygroup.description,
mygroup.linked_to_group
from mygroup
where
(upper(mygroup.linked_to_group) = upper(:iparent))
INTO :PK_GROUP,
:DESCRIPTION,
:LINKED_TO_GROUP
DO
BEGIN
suspend;
i = 0;
/* Stored procedures can be nested up to 1,000 levels deep. This limitation helps to prevent infinite loops that can occur when a recursive procedure provides no absolute terminating condition.*/
while (i < 1000) do
begin
execute procedure mygroup_proc(:pk_group) returning_values (:pk_group,:description,:linked_to_group);
if (:pk_group is null) then break;
suspend;
i = i+1;
end
END
END^
SET TERM ; ^
当输入参数是{CD1E33D1-1666-49B9-83BE-067687E4DDD6} （ Vishal Group PK
）结果是：
更新
输出中是否可以获得“Vishal Group”记录？
是的，首先阅读您可以在其他程序中执行上述过程，如：
SET TERM ^ ;
create or alter procedure MYGROUP_PROC_1 (
IPARENT varchar(100))
returns (
PK_GROUP varchar(64),
DESCRIPTION varchar(255),
LINKED_TO_GROUP varchar(64))
as
BEGIN
FOR
select
mygroup.pk_group,
mygroup.description,
mygroup.linked_to_group
from mygroup
where
(upper(mygroup.description) = upper(:iparent))
/*
or (upper(mygroup.pk_group) = upper(:iparent)) instead
if you want to use pk_group as input parameter
*/
INTO :PK_GROUP,
:DESCRIPTION,
:LINKED_TO_GROUP
DO
BEGIN
suspend;
for
select pk_group, description,linked_to_group from mygroup_proc(:pk_group)
into
:PK_GROUP,
:DESCRIPTION,
:LINKED_TO_GROUP
do
begin
suspend;
end
end
END^
SET TERM ; ^
然后使用
SELECT * FROM MYGROUP_PROC_1('Vishal Group')
要么
SELECT * FROM MYGROUP_PROC_1('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}')
如果你使用pk_group作为参数
作为递归存储过程的替代方法，下面是使用CTE（公用表表达式）的存储过程：
SET TERM ^ ;
CREATE OR ALTER procedure RECURSIVE_MYGROUP (
PK_GROUP_IN GUID)
returns (
PK_GROUP GUID,
DESCRIPTION varchar(255),
LINKED_TO_GROUP GUID)
as
begin
for with recursive RECUR_MYGROUP
as (select M.PK_GROUP, M.DESCRIPTION, M.LINKED_TO_GROUP
from MYGROUP M
where M.PK_GROUP = :PK_GROUP_IN
union all
select M.PK_GROUP, M.DESCRIPTION, M.LINKED_TO_GROUP
from RECUR_MYGROUP RM
inner join MYGROUP M on M.LINKED_TO_GROUP = RM.PK_GROUP)
select *
from RECUR_MYGROUP
into :PK_GROUP, :DESCRIPTION, :LINKED_TO_GROUP
do
begin
suspend;
end
end^
SET TERM ; ^
然后你可以使用：
select * from recursive_mygroup('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
这只是为了分享知识，下面的SQL不是我写的，但是我是从Firebird雅虎团队那里得到的。 但我非常尊重并高度赞赏你给我的帮助。
SQL：
WITH RECURSIVE
G1_PARENT AS
(
SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1'
),
R_TREE AS
(
SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT
FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
WHERE TT.LINKED_TO_GROUP IS NULL
UNION ALL
SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE RT.PARENT END AS PARENT
FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP
)
SELECT
*
FROM
R_TREE RT2
INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION
INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A
最好的祝福。
维沙尔
链接地址: http://www.djcxy.com/p/88133.html