Begin
This is last year's problem, today in the mail to find this problem, feel the top interesting, special record down.
In table Relationgraph, there are three fields (Id,node,relatednode), where node and relatednode two fields describe the connection of two nodes; now ask, find out from node "P" to Node "J", Shortest path (that is, the least number of nodes passed).
Figure 1.
Analytical:
To better describe the relationship between the field node and the Relatednode in table relationgraph, I use a graphic to describe
As shown in Figure 2.
Figure 2.
In Fig. 2, we can see clearly how each node connects directly, and can clearly see several possible paths of node "P" to Node "J".
The 2nd possible path can be seen from above, with the least number of nodes passing through.
In order to solve the problem of the beginning, I have consulted two methods,
The 1th method is that
Reference Single-source Shortest Path algorithm:Dijkstra ( Dijkstra ) algorithm, the main feature is the starting point as the center to the outer layer expansion, until the extension to the end.
Figure 3.
The 2nd method is that
The improvement of the 1th method is to adopt the Multi-Source point method, where the node "P" and the Node "J" are centered toward the outer layer, until the two round tangent points, as shown in Figure 4. ：
Figure 4.
Realize:
In the next, I'll describe how to implement it in SQL Server. Of course, the 2nd method I've used here, with "P" and "J" as the starting point as the center outer layer extension.
This provides a script for the create& Insert data with table Relactiongraph:
Copy Code code as follows:
Use testdb
Go
If object_id (' Re Lactiongraph ') is a NOT null drop table Relactiongraph
CREATE table relactiongraph (ID int identity,item nvarchar (5 0), Relactionitem nvarchar (), Constraint Pk_relactiongraph primary key (ID))
Go
Create nonclustered IND Ex Ix_relactiongraph_item on Relactiongraph (Item) include (Relactionitem)
Create nonclustered index Ix_relaction Graph_relactionitem on Relactiongraph (relactionitem) include (item)
Go
Inserts into Relactiongraph (item , Relactionitem) Values
(' A ', ' B '), (' A ', ' C '), (' A ', ' d '), (' A ', ' e '),
; (' B ', ' F '), (' B ', ' G '), (' B ', ' H '),
(' C ', ' I '), (' C ', ' J '),
(' f ', ' K '), (' F ', ' l '),
(' k ', ' O '), (' K ', ' P '),
(' o ', ' I '), (' O ', ' l ')
Go
Write a stored procedure Up_getpath
Copy Code code as follows:
Use TestDB
Go
--procedure:
If object_id (' Up_getpath ') is not null
Drop proc Up_getpath
Go
Create proc Up_getpath
(
@Node nvarchar (50),
@RelatedNode nvarchar (50)
)
As
SET NOCOUNT ON
Declare
@level smallint = 1,--depth of current search
@MaxLevel smallint=100,--Maximum searchable depth
@Node_WhileFlag bit=1,--@node as the center of the search, as the ability to cycle the search mark
@RelatedNode_WhileFlag Bit=1--@relatednode as a center for searching, as a marker of whether or not to cycle the search
--If you directly find two node there is a direct relationship and return directly
If Exists (select 1 from Relationgraph where (node= @Node and relatednode= @RelatedNode) or (node= @RelatedNode and Relatednod E= @Node)) or @Node = @RelatedNode
Begin
Select convert (nvarchar), @Node + '--> ' + @RelatedNode) as Relationgraphpath,convert (smallint,0) as Stopcount
Return
End
--
If object_id (' tempdb.. #1 ') is a NOT null Drop table #1-Temporary table #, which stores the node data that is @node as the center outward
If object_id (' tempdb.. #2 ') is a NOT null Drop table #2--temporary table @relatednode, which stores the node data that is extended as a center outward
CREATE TABLE #1 (
Node nvarchar (,--) relative source point
Relatednode nvarchar (50),--relative target
Level smallint--depth
)
CREATE table #2 (Node nvarchar, relatednode nvarchar, level smallint)
Insert into #1 (Node, Relatednode, level)
Select Node, Relatednode, @level from Relationgraph a WHERE A.node = @Node Union--forward: @node as source query
Select Relatednode, Node, @level from Relationgraph a WHERE A.relatednode = @Node--Reverse: Query with @node as Target
Set @Node_WhileFlag =sign (@ @rowcount)
Insert into #2 (Node, Relatednode, level)
Select Node, Relatednode, @level from Relationgraph a WHERE A.node = @RelatedNode Union--forward: @relatednode as source query
Select Relatednode, Node, @level from Relationgraph a WHERE A.relatednode = @RelatedNode--Reverse: Query with @relatednode as Target
Set @RelatedNode_WhileFlag =sign (@ @rowcount)
--If @node or @RelatedNode data is not found in the table Relationgraph, skip the subsequent while procedure
If not exists (select 1 to #1) or not EXISTS (select 1 from #2)
Begin
Goto While_out
End
While not EXISTS (select 1 from #1 a INNER join #2 B on B.relatednode=a.relatednode)--Determine if pointcuts occur
and (@Node_WhileFlag | @RelatedNode_WhileFlag) >0--Determine if you can search
and @level < @MaxLevel--control depth
Begin
If @Node_WhileFlag >0
Begin
Insert into #1 (Node, Relatednode, level)
--The positive
Select A.node,a.relatednode, @level +1
From Relationgraph A
where exists (select 1 from #1 where Relatednode=a.node and level= @level) and
Not EXISTS (select 1 from #1 where Node=a.node)
Union
--Reverse
Select A.relatednode,a.node, @level +1
From Relationgraph A
where exists (select 1 from #1 where Relatednode=a.relatednode and level= @level) and
Not EXISTS (select 1 from #1 where Node=a.relatednode)
Set @Node_WhileFlag =sign (@ @rowcount)
End
If @RelatedNode_WhileFlag >0
Begin
Insert into #2 (Node, Relatednode, level)
--The positive
Select A.node,a.relatednode, @level +1
From Relationgraph A
where exists (select 1 from #2 where Relatednode=a.node and level= @level) and
Not EXISTS (select 1 from #2 where Node=a.node)
Union
--Reverse
Select A.relatednode,a.node, @level +1
From Relationgraph A
where exists (select 1 from #2 where Relatednode=a.relatednode and level= @level) and
Not EXISTS (select 1 from #2 where Node=a.relatednode)
Set @RelatedNode_WhileFlag =sign (@ @rowcount)
End
Select @level +=1
End
While_out:
--The following is the result path of the constructed return
If object_id (' tempdb.. #Path1 ') is a NOT null Drop Table #Path1
If object_id (' tempdb.. #Path2 ') is a NOT null Drop Table #Path2
; with Cte_path1 as
(
Select a.node,a.relatednode,level,convert (nvarchar), a.node+ '-> ' +a.relatednode) as Relationgraphpath, Convert (smallint,1) as Pathlevel from #1 A where exists (select 1 from #2 where Relatednode=a.relatednode)
UNION ALL
Select b.node,a.relatednode,b.level,convert (nvarchar), b.node+ '-> ' +a.relationgraphpath) as Relationgraphpath, Convert (smallint,a.pathlevel+1) as Pathlevel
From Cte_path1 A
INNER JOIN #1 B on B.relatednode=a.node
and B.LEVEL=A.LEVEL-1
)
SELECT * Into #Path1 from cte_path1
; with Cte_path2 as
(
Select a.node,a.relatednode,level,convert (nvarchar), a.node) as Relationgraphpath,convert (smallint,1) as Pathlevel from #2 A where exists (select 1 from #1 where Relatednode=a.relatednode)
UNION ALL
Select b.node,a.relatednode,b.level,convert (nvarchar), a.relationgraphpath+ '-> ' +b.node) as Relationgraphpath, Convert (smallint,a.pathlevel+1)
From Cte_path2 A
INNER JOIN #2 B on B.relatednode=a.node
and B.LEVEL=A.LEVEL-1
)
SELECT * Into #Path2 from cte_path2
; with Cte_result as
(
Select a.relationgraphpath+ '-> ' +b.relationgraphpath as relationgraphpath,a.pathlevel+b.pathlevel-1 as StopCount, Rank () Over (order by A.pathlevel+b.pathlevel) as Result_row
From #Path1 A
INNER JOIN #Path2 B on B.relatednode=a.relatednode
and b.level=1
where a.level=1
)
Select distinct Relationgraphpath,stopcount from Cte_result where result_row=1
Go
The above stored procedure is mainly divided into two parts, the 1th part is how to search, and the 2nd part realizes how to construct the return result. The 1th part of the code in accordance with the previous method 2, through the @node and @RelatedNode two nodes to the outer search, each search returned to the nodes are saved to the temporary table and the method, and then judge the temporary table # # and there is no tangent point, If it appears, the shortest path has been found (with the fewest number of nodes), otherwise the loop search continues until the maximum search depth (@MaxLevel smallint=100) is reached or the pointcut is found. If the pointcut is not found on the 100 floor, the search is discarded. The maximum searchable depth @maxlevel is used here to control the poor performance due to the large amount of data, which is inversely proportional to the search performance. The code also refers to a forward and reverse search, mainly relative to node and Relatednode, they are mutually referenced objects, for outward search use.
The following is the execution of the stored procedure:
Copy Code code as follows:
Use TestDB
Go
EXEC Dbo.up_getpath
@Node = ' P ',
@RelatedNode = ' J '
Go
You can give @node and @RelatedNode different values, depending on your needs.
Expand:
The previous example can be extended to the city's bus routes, offering two sites, searching through the least-site bus routes of these two sites, and extending the search for interpersonal relationships in the community, such as one who wants to know another person, So how many people do they have to go through directly? In addition to people directly with direct friends, relatives related, it is also possible to find a link between people and people, such as several writers by publishing a book, so that these people can be found in a book by the author list of their existence of a joint publication of the Association, which is to search two people to understand the path to provide a reference. This problem can be very complex, but it could be extended like this.
Summary:
This is just a way to find the least number of paths in all two nodes, and in practical applications, it may be more complicated than this. In other environments or scenarios, there may be some information such as length, time, multiple nodes, multiple scopes, and so on. In any case, generally have to refer to some principles, algorithms to achieve.
