sql server: 最短路径

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409

--------------------------------------------------------------------- -- Road System  道路 ---------------------------------------------------------------------   -- Listing 9-3: DDL & Sample Data for Cities, Roads SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.Roads') IS NOT NULL   DROP TABLE dbo.Roads; GO IF OBJECT_ID('dbo.Cities') IS NOT NULL   DROP TABLE dbo.Cities; GO   CREATE TABLE dbo.Cities (   cityid  CHAR(3)     NOT NULL PRIMARY KEY,   city    VARCHAR(30) NOT NULL,   region  VARCHAR(30) NULL,   country VARCHAR(30) NOT NULL );   INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('ATL', 'Atlanta', 'GA', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('ORD', 'Chicago', 'IL', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('DEN', 'Denver', 'CO', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('IAH', 'Houston', 'TX', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('MCI', 'Kansas City', 'KS', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('LAX', 'Los Angeles', 'CA', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('MIA', 'Miami', 'FL', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('MSP', 'Minneapolis', 'MN', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('JFK', 'New York', 'NY', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('SEA', 'Seattle', 'WA', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('SFO', 'San Francisco', 'CA', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('ANC', 'Anchorage', 'AK', 'USA'); INSERT INTO dbo.Cities(cityid, city, region, country)   VALUES('FAI', 'Fairbanks', 'AK', 'USA');   CREATE TABLE dbo.Roads (   city1       CHAR(3) NOT NULL REFERENCES dbo.Cities,   city2       CHAR(3) NOT NULL REFERENCES dbo.Cities,   distance INT     NOT NULL,   PRIMARY KEY(city1, city2),   CHECK(city1 < city2),   CHECK(distance > 0) );   INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ANC', 'FAI', 359); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ATL', 'ORD', 715); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ATL', 'IAH', 800); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ATL', 'MCI', 805); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ATL', 'MIA', 665); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('ATL', 'JFK', 865); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'IAH', 1120); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'MCI', 600); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'LAX', 1025); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'MSP', 915); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'SEA', 1335); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('DEN', 'SFO', 1270); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('IAH', 'MCI', 795); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('IAH', 'LAX', 1550); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('IAH', 'MIA', 1190); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('JFK', 'ORD', 795); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('LAX', 'SFO', 385); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('MCI', 'ORD', 525); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('MCI', 'MSP', 440); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('MSP', 'ORD', 410); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('MSP', 'SEA', 2015); INSERT INTO dbo.Roads(city1, city2, distance) VALUES('SEA', 'SFO', 815); GO       SELECT * FROM dbo.Roads GO   WITH Roads2  --每对城市生成行 AS (     SELECT city1 AS from_city,city2 AS to_city FROM dbo.Roads     UNION ALL     SELECT city2,city2 FROM dbo.Roads ), RoadPaths AS ( --返回第一级别的所有可到达的城市对     SELECT from_city,to_city,     CAST('.'+from_city+'.'+to_city+'.' AS VARCHAR(max)) AS path     FROM Roads2     UNION ALL     SELECT F.from_city,T.to_city,--返回下一级别的可到达的城市对     CAST(F.path+T.to_city+'.' AS VARCHAR(MAX)) --路径需要验明循环     FROM RoadPaths AS F     JOIN Roads2 AS T     ON CASE WHEN F.path LIKE '%.'+T.to_city+'.%' --如果to_city出现在from_city的路径中，则检测到循环         THEN 1 ELSE 0 END =0     AND F.to_city =T.from_city ) SELECT DISTINCT from_city,to_city FROM RoadPaths;--返回Roads 的传递包 GO       --------------------------------------------------------------------- -- Undirected Cyclic Graph ---------------------------------------------------------------------   -- Listing 9-38: Transitive Closure of Roads (Undirected Cyclic Graph) 传递闭包 WITH Roads2 -- Two rows for each pair (f-->t, t-->f) AS (   SELECT city1 AS from_city, city2 AS to_city FROM dbo.Roads   UNION ALL   SELECT city2, city1 FROM dbo.Roads ), RoadPaths AS (   -- Return all first-level reachability pairs   SELECT from_city, to_city,     -- path is needed to identify cycles     CAST('.' + from_city + '.' + to_city + '.' AS VARCHAR(MAX)) AS path   FROM Roads2     UNION ALL     -- Return next-level reachability pairs   SELECT F.from_city, T.to_city,     CAST(F.path + T.to_city + '.' AS VARCHAR(MAX))   FROM RoadPaths AS F     JOIN Roads2 AS T       -- if to_city appears in from_city's path, cycle detected       ON CASE WHEN F.path LIKE '%.' + T.to_city + '.%'               THEN 1 ELSE 0 END = 0       AND F.to_city = T.from_city ) -- Return Transitive Closure of Roads SELECT DISTINCT from_city, to_city FROM RoadPaths; GO   -- Listing 9-39: Creation Script for the fn_RoadsTC UDF IF OBJECT_ID('dbo.fn_RoadsTC') IS NOT NULL   DROP FUNCTION dbo.fn_RoadsTC; GO   CREATE FUNCTION dbo.fn_RoadsTC() RETURNS @RoadsTC TABLE (   from_city VARCHAR(3) NOT NULL,   to_city   VARCHAR(3) NOT NULL,   PRIMARY KEY (from_city, to_city) ) AS BEGIN   DECLARE @added as INT;     INSERT INTO @RoadsTC(from_city, to_city)     SELECT city1, city2 FROM dbo.Roads;     SET @added = @@rowcount;     INSERT INTO @RoadsTC     SELECT city2, city1 FROM dbo.Roads     SET @added = @added + @@rowcount;     WHILE @added > 0 BEGIN       INSERT INTO @RoadsTC       SELECT DISTINCT TC.from_city, R.city2       FROM @RoadsTC AS TC         JOIN dbo.Roads AS R           ON R.city1 = TC.to_city       WHERE NOT EXISTS         (SELECT * FROM @RoadsTC AS TC2          WHERE TC2.from_city = TC.from_city            AND TC2.to_city = R.city2)         AND TC.from_city <> R.city2;       SET @added = @@rowcount;       INSERT INTO @RoadsTC       SELECT DISTINCT TC.from_city, R.city1       FROM @RoadsTC AS TC         JOIN dbo.Roads AS R           ON R.city2 = TC.to_city       WHERE NOT EXISTS         (SELECT * FROM @RoadsTC AS TC2          WHERE TC2.from_city = TC.from_city            AND TC2.to_city = R.city1)         AND TC.from_city <> R.city1;       SET @added = @added + @@rowcount;   END   RETURN; END GO   -- Use the fn_RoadsTC UDF  --传递闭包函数 SELECT * FROM dbo.fn_RoadsTC(); GO   -- Listing 9-40: All paths and distances in Roads (15262 rows）返回所有路径和距离 WITH Roads2 AS (   SELECT city1 AS from_city, city2 AS to_city, distance FROM dbo.Roads   UNION ALL   SELECT city2, city1, distance FROM dbo.Roads ), RoadPaths AS (   SELECT from_city, to_city, distance,     CAST('.' + from_city + '.' + to_city + '.' AS VARCHAR(MAX)) AS path   FROM Roads2     UNION ALL     SELECT F.from_city, T.to_city, F.distance + T.distance,     CAST(F.path + T.to_city + '.' AS VARCHAR(MAX))   FROM RoadPaths AS F     JOIN Roads2 AS T       ON CASE WHEN F.path LIKE '%.' + T.to_city + '.%'               THEN 1 ELSE 0 END = 0       AND F.to_city = T.from_city ) -- Return all paths and distances SELECT * FROM RoadPaths; GO     -- Listing 9-41: Shortest paths in Roads 返回最短路径 WITH Roads2 AS (   SELECT city1 AS from_city, city2 AS to_city, distance FROM dbo.Roads   UNION ALL   SELECT city2, city1, distance FROM dbo.Roads ), RoadPaths AS (   SELECT from_city, to_city, distance,     CAST('.' + from_city + '.' + to_city + '.' AS VARCHAR(MAX)) AS path   FROM Roads2     UNION ALL     SELECT F.from_city, T.to_city, F.distance + T.distance,     CAST(F.path + T.to_city + '.' AS VARCHAR(MAX))   FROM RoadPaths AS F     JOIN Roads2 AS T       ON CASE WHEN F.path LIKE '%.' + T.to_city + '.%'               THEN 1 ELSE 0 END = 0       AND F.to_city = T.from_city ), RoadsMinDist -- Min distance for each pair in TC 每对城市的最短距离 AS (   SELECT from_city, to_city, MIN(distance) AS mindist   FROM RoadPaths   GROUP BY from_city, to_city ) -- Return shortest paths and distances 返回最短路径和距离 SELECT RP.* FROM RoadsMinDist AS RMD   JOIN RoadPaths AS RP     ON RMD.from_city = RP.from_city     AND RMD.to_city = RP.to_city     AND RMD.mindist = RP.distance; GO       -- Listing 9-42: Load Shortest Road Paths Into a Table 把最短路径保存到表 WITH Roads2 AS (   SELECT city1 AS from_city, city2 AS to_city, distance FROM dbo.Roads   UNION ALL   SELECT city2, city1, distance FROM dbo.Roads ), RoadPaths AS (   SELECT from_city, to_city, distance,     CAST('.' + from_city + '.' + to_city + '.' AS VARCHAR(MAX)) AS path   FROM Roads2     UNION ALL     SELECT F.from_city, T.to_city, F.distance + T.distance,     CAST(F.path + T.to_city + '.' AS VARCHAR(MAX))   FROM RoadPaths AS F     JOIN Roads2 AS T       ON CASE WHEN F.path LIKE '%.' + T.to_city + '.%'               THEN 1 ELSE 0 END = 0       AND F.to_city = T.from_city ), RoadsMinDist AS (   SELECT from_city, to_city, MIN(distance) AS mindist   FROM RoadPaths   GROUP BY from_city, to_city ) SELECT RP.* INTO dbo.RoadPaths FROM RoadsMinDist AS RMD   JOIN RoadPaths AS RP     ON RMD.from_city = RP.from_city     AND RMD.to_city = RP.to_city     AND RMD.mindist = RP.distance;   CREATE UNIQUE CLUSTERED INDEX idx_uc_from_city_to_city   ON dbo.RoadPaths(from_city, to_city); GO   -- Return shortest path between Los Angeles and New York 测试查询最短路径 SELECT * FROM dbo.RoadPaths WHERE from_city = 'LAX' AND to_city = 'JFK'; GO   -- Listing 9-43: Creation Script for the fn_RoadsTC UDF IF OBJECT_ID('dbo.fn_RoadsTC') IS NOT NULL   DROP FUNCTION dbo.fn_RoadsTC; GO CREATE FUNCTION dbo.fn_RoadsTC() RETURNS @RoadsTC TABLE (   uniquifier INT          NOT NULL IDENTITY,   from_city  VARCHAR(3)   NOT NULL,   to_city    VARCHAR(3)   NOT NULL,   distance   INT          NOT NULL,   route      VARCHAR(MAX) NOT NULL,   PRIMARY KEY (from_city, to_city, uniquifier) ) AS BEGIN   DECLARE @added AS INT;     INSERT INTO @RoadsTC     SELECT city1 AS from_city, city2 AS to_city, distance,       '.' + city1 + '.' + city2 + '.'     FROM dbo.Roads;     SET @added = @@rowcount;     INSERT INTO @RoadsTC     SELECT city2, city1, distance, '.' + city2 + '.' + city1 + '.'     FROM dbo.Roads;     SET @added = @added + @@rowcount;     WHILE @added > 0 BEGIN     INSERT INTO @RoadsTC       SELECT DISTINCT TC.from_city, R.city2,         TC.distance + R.distance, TC.route + city2 + '.'       FROM @RoadsTC AS TC         JOIN dbo.Roads AS R           ON R.city1 = TC.to_city       WHERE NOT EXISTS         (SELECT * FROM @RoadsTC AS TC2          WHERE TC2.from_city = TC.from_city            AND TC2.to_city = R.city2            AND TC2.distance <= TC.distance + R.distance)         AND TC.from_city <> R.city2;       SET @added = @@rowcount;       INSERT INTO @RoadsTC       SELECT DISTINCT TC.from_city, R.city1,         TC.distance + R.distance, TC.route + city1 + '.'       FROM @RoadsTC AS TC         JOIN dbo.Roads AS R           ON R.city2 = TC.to_city       WHERE NOT EXISTS         (SELECT * FROM @RoadsTC AS TC2          WHERE TC2.from_city = TC.from_city            AND TC2.to_city = R.city1            AND TC2.distance <= TC.distance + R.distance)         AND TC.from_city <> R.city1;       SET @added = @added + @@rowcount;   END   RETURN; END GO   -- Return shortest paths and distances SELECT from_city, to_city, distance, route FROM (SELECT from_city, to_city, distance, route,         RANK() OVER (PARTITION BY from_city, to_city                      ORDER BY distance) AS rk       FROM dbo.fn_RoadsTC()) AS RTC WHERE rk = 1; GO   -- Cleanup DROP TABLE dbo.RoadPaths; GO