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  阅读(189)  评论(0)  编辑  收藏  举报