declare @ID bigint
declare @Group int
------Build_Tree-------
declare @Radif bigint
declare @ID1 bigint
declare @parent bigint
declare @check bit
declare @Level int
declare @num bigint
--------------
------build_Edge--------
declare @ID2 bigint
declare @parent2 bigint
declare @Level2 int
declare @ID12 bigint
declare @parent12 bigint
declare @Level12 int
declare @num2 int
------------------------
Create TABLE #Virtual_Tree_Sense (
[Radif] [int] IDENTITY(1,1) NOT NULL,
[ID] [bigint] NULL,
[Parent] [bigint] NULL,
[Level] [int] NULL,
[check] [bit] NULL default(0)
)
Create Table #Graph (
[Radif] [int] IDENTITY(1,1) NOT NULL,
[ID] [bigint] NULL,
[Parent] [bigint] NULL,
[Level] [int] NULL,
[num] [int] NULL
)
Truncate table #Graph
Truncate table #Virtual_Tree_Sense
--TRuncate Table Graph
--Truncate Table Find_node
DECLARE MyCur2 CURSOR
FOR
SELECT [ID],[Group]
FROM [Graph].[dbo].[Sense_Word]
OPEN MyCur2
FETCH NEXT FROM MyCur2 INTO @ID , @Group
WHILE (@@FETCH_STATUS <> -1) AND (@@FETCH_STATUS <> -2)
BEGIN --while1
--EXECUTE [Graph].[dbo].[Build_Tree] @ID
----------------------------------------------
--set @num = 108820496
set @num = @ID
delete from #Virtual_Tree_Sense
INSERT INTO #Virtual_Tree_Sense
([ID]
,[Parent], [Level])
Select id , parent , 1 from [Graph].[dbo].[Relation_Wordnet]
where parent = @num
DECLARE MyCur CURSOR
FOR
SELECT [Radif],[ID],[Parent],[Check],[Level] FROM #Virtual_Tree_Sense
Where ([check] = 0 or [check] is null) And [Level] < 4
OPEN MyCur
FETCH NEXT FROM MyCur INTO @radif , @ID1 , @parent , @Check , @level
WHILE (@@FETCH_STATUS <> -1) AND (@@FETCH_STATUS <> -2)
BEGIN --while1
INSERT INTO #Virtual_Tree_Sense
([ID]
,[Parent],[Level])
Select id , parent , @level+1 from [Graph].[dbo].[Relation_Wordnet]
where parent = @ID1
And
(ID not in
(SELECT [ID] FROM #Virtual_Tree_Sense))
and
ID <> @num
UPDATE #Virtual_Tree_Sense
SET [Check] = 1
WHERE radif = @Radif
FETCH NEXT FROM MyCur INTO @radif , @ID1 , @parent , @Check , @level
END -- While 1
CLOSE Mycur
DEALLOCATE MyCur
------------------------------------------------
--EXECUTE [Graph].[dbo].[Build_Edge] @Group
-------------------------------------------------
set @num2 = (select max(num) from #Graph)
IF (@num2 is null) set @num2 =0
DECLARE MyCur1 CURSOR
FOR
SELECT ID,Parent,[Level] FROM #Virtual_Tree_Sense
where ID in
(select ID from Sense_Word where [Group] <> @Group)
group by ID,Parent,[Level]
order by level
OPEN MyCur1
FETCH NEXT FROM MyCur1 INTO @ID2 , @parent2 , @level2
WHILE (@@FETCH_STATUS <> -1) AND (@@FETCH_STATUS <> -2)
BEGIN --while1
set @num2 = @num2 + 1
INSERT INTO #Graph
([ID]
,[Parent],[Level],[num])
values(@id2,@Parent2,@Level2,@num2)
set @Level2 = @Level2 - 1
While @level2 > 0
begin
Select @ID2 =ID , @Parent2= parent , @Level12 =[level] from #Virtual_Tree_Sense Where ID = @Parent2 And Level = @Level2
group by ID,Parent,[Level]
order by [level]
INSERT INTO #Graph
([ID]
,[Parent],[Level],[num])
values(@id2,@Parent2,@Level2,@num2)
set @Level2 = @Level2 - 1
End
FETCH NEXT FROM MyCur1 INTO @ID2 , @parent2 , @level2
END -- While 1
CLOSE Mycur1
DEALLOCATE MyCur1
-------------------------------------------------
FETCH NEXT FROM MyCur2 INTO @ID , @Group
END -- While 1
CLOSE Mycur2
DEALLOCATE MyCur2
----------------------End---------------------------
----------------------Begin------------------------------
--declare @num int
Truncate table Edge
DECLARE MyCur1 CURSOR
FOR
SELECT [num]
FROM #Graph
group by [num]
OPEN MyCur1
FETCH NEXT FROM MyCur1 INTO @num
WHILE (@@FETCH_STATUS <> -1) AND (@@FETCH_STATUS <> -2)
BEGIN --while1
--EXECUTE [Graph].[dbo].[Build_Edge_Table] @num
------------------------------------------
--declare @ID bigint
--declare @parent bigint
--declare @Level int
INSERT INTO [Graph].[dbo].[Edge]
([Edge]
,[E1]
,[E2])
SELECT num
,[Parent],[ID]
FROM #Graph
Where num = @num and level = 1
DECLARE MyCur CURSOR
FOR
SELECT [ID]
,[Parent],[level]
FROM #Graph
Where num = @num And level >1
order by num,[level]
OPEN MyCur
FETCH NEXT FROM MyCur INTO @ID , @parent, @level
WHILE (@@FETCH_STATUS <> -1) AND (@@FETCH_STATUS <> -2)
BEGIN --while1
IF (@level = 2)
begin
UPDATE [Graph].[dbo].[Edge] SET [E3] = @ID Where Edge = @num
End
Else IF (@level = 3)
begin
UPDATE [Graph].[dbo].[Edge] SET [E4] = @ID Where Edge = @num
End
Else IF (@level = 4)
begin
UPDATE [Graph].[dbo].[Edge] SET [E5] = @ID Where Edge = @num
End
FETCH NEXT FROM MyCur INTO @ID , @parent , @level
END -- While 1
CLOSE Mycur
DEALLOCATE MyCur
------------------------------------------
FETCH NEXT FROM MyCur1 INTO @num
END -- While 1
CLOSE Mycur1
DEALLOCATE MyCur1
Drop table #Graph
Drop table #Virtual_Tree_Sense
----------------------End------------------------------
قوانین ایجاد تاپیک در تالار
پاسخ با نقل قول