t-sql最短路径搜索
1 SET STATISTICS IO ON; 2 SET STATISTICS TIME ON; 3 GO 4 IF OBJECT_ID(N'dbo.t1') IS NOT NULL 5 DROP TABLE dbo.t1; 6 GO 7 CREATE TABLE t1 8 ( 9 id INT IDENTITY(1, 1) 10 PRIMARY KEY , 11 a NVARCHAR(2) , 12 b NVARCHAR(2) 13 ); 14 --建立索引a 15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190507-201219] ON [dbo].[t1] 16 ( 17 [a] ASC 18 ) 19 INCLUDE ( [b]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]; 20 GO 21 --建立索引b 22 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190507-201235] ON [dbo].[t1] 23 ( 24 [b] ASC 25 ) 26 INCLUDE ( [a]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]; 27 GO 28 29 30 31 32 33 WITH cte_0 ( a, b ) 34 AS ( SELECT 1 , 35 2 36 UNION ALL 37 SELECT 1 , 38 3 39 UNION ALL 40 SELECT 2 , 41 4 42 UNION ALL 43 SELECT 2 , 44 5 45 UNION ALL 46 SELECT 2 , 47 3 48 UNION ALL 49 SELECT 3 , 50 6 51 UNION ALL 52 SELECT 4 , 53 9 54 UNION ALL 55 SELECT 5 , 56 7 57 UNION ALL 58 SELECT 6 , 59 9 60 UNION ALL 61 SELECT 7 , 62 8 63 UNION ALL 64 SELECT 8 , 65 10 66 UNION ALL 67 SELECT 9 , 68 10 69 UNION ALL 70 SELECT 9 , 71 12 72 UNION ALL 73 SELECT 10 , 74 11 75 UNION ALL 76 SELECT 11 , 77 12 78 ) 79 INSERT INTO t1 80 ( a , 81 b 82 ) 83 SELECT a , 84 b 85 FROM cte_0 86 UNION ALL 87 SELECT b , 88 a 89 FROM cte_0; 90 --加上是否循环的标记(效率较不加循环标记要高) 91 WITH cte_a 92 AS ( SELECT a , 93 b 94 FROM dbo.t1 95 --UNION ALL 96 --SELECT b , 97 -- a 98 --FROM dbo.t1 99 ), 100 cte_b 101 AS ( SELECT a , 102 b , 103 0 AS isok , 104 CAST(a + '>' + b AS NVARCHAR(MAX)) AS c_path , 105 1 AS nlevel 106 FROM cte_a 107 WHERE a = '4'--起点 108 UNION ALL 109 SELECT s.a , 110 s.b , 111 CASE WHEN s.b = '11' THEN 1--如果下一层找到了终点,那么不需要继续向下找 112 ELSE 0 113 END AS isok , 114 CAST(p.c_path + '>' + s.b AS NVARCHAR(MAX)) AS c_path , 115 p.nlevel + 1 116 FROM cte_a AS s 117 INNER JOIN cte_b AS p ON p.b = s.a 118 WHERE CHARINDEX(s.b, p.c_path) = 0 119 AND p.isok = 0 120 ), 121 cte_c 122 AS ( SELECT * 123 FROM cte_b 124 --WHERE b = '11'--终点 125 ) 126 SELECT * 127 FROM cte_c 128 --WHERE cte_c.nlevel = ( SELECT MIN(cte_c.nlevel) 129 -- FROM cte_c 130 -- );
像个乞丐
posted on 2019-06-28 17:40 hold_on_up 阅读(188) 评论(0) 编辑 收藏 举报