软件工程之数据流图和数据字典_Sql server 2019图数据库之，成语接龙的sql 分析...

Sql server 2019图数据库之，成语接龙的sql 分析

规律一，起始成语接龙到另外一个成语（包括起始成语自己），如果能够接龙起来的话，接龙4个以内即可完成（这是个错误的结论，20201012-发现因为脚本的错误导致出现了错误的结论）。期待数学证明。

比如：三心二意->意气用事->事不过三->三心二意，三心二意->意气自如->如花似玉->玉箫金管->管鲍之交

下面是验证的脚本：

--从一个成语开始到另外一个成语最短路径分析。
IF OBJECT_ID(N'dbo.t_list') IS NOT NULL
 DROP TABLE dbo.t_list;
GO
CREATE TABLE dbo.t_list
(
 i INT IDENTITY,
 ccy NVARCHAR(20) NOT NULL
 UNIQUE,
 maxlevel INT NOT NULL
 DEFAULT 0
);
GO
ALTER TABLE dbo.t_list ADD PRIMARY KEY (ccy);
GO
DECLARE @ccy NVARCHAR(20) = N'';
DECLARE c1 CURSOR FOR SELECT [成语] FROM [dbo].[cheng_yu_node];
OPEN c1;
FETCH NEXT FROM c1
INTO @ccy;
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO dbo.t_list
 (
 ccy,
 maxlevel
 )
 SELECT @ccy,
 ISNULL(MAX(levels), 0) AS maxlevel
 FROM
 (
 SELECT p1.成语 AS firstnode,
 STRING_AGG(p2.成语, '->') WITHIN GROUP(graph PATH) AS cpath,
 LAST_VALUE(p2.成语) within GROUP(graph PATH) AS lastnode,
 count(p2.成语) within GROUP(graph PATH) AS levels
 FROM [dbo].[cheng_yu_node] AS p1,
 [dbo].[cheng_yu_edge] FOR PATH AS fo,
 [dbo].[cheng_yu_node] FOR PATH AS p2
 WHERE MATCH(SHORTEST_PATH(p1(-(fo)->p2){ 1, 4 }))
 AND p1.成语 = @ccy --开始成语
 ) AS q;
 
 FETCH NEXT FROM c1
 INTO @ccy;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT *
FROM dbo.t_list;
GO
UPDATE dbo.t_list
SET maxlevel = ISNULL(maxlevel, 0);

在脚本运行了将近八个小时候计算出了结果。

上面结论是错误的，脚本中的{ 1,4 }应该修改为+