计算从A地出发到各个地方的路径及距离

数据库环境：SQL SERVER 2005

如题，现有bus表数据如下，dstart是起点，dend是终点，distance是两地的距离。

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIoAAAB0CAIAAADVfT1aAAAF/ElEQVR4nO1czWrjMBDWQ7ULzruUkkP8IIVCTyZPUVi2ewr4GfZQ6DWQF+iloO51A96DZUnWX2RHP99258MU15YzI32aGXkmCvvx80AH7MHOGt4//5zrobD0up2NBNEDDaIHGkQPNKLpOXZN0x1zqpJeevCpup2NxHX0xHQjuqtw9KzucjoQPQsfxKLn2DVsQtMdtQtNdxCnTXc8nw872Y7tDmM3drtm9mwYMdK1K5M2TdftNLnOp9aJm5hY0mVLGa2Vpd7lQQnTc+ya6UMPO9Z0x/NhN14Y7wRM6tg12giush5buvZZSorWbdHGeGqtOPGBi7rsV2b+oVqbEIL06M9r8hTvRgPX1FtPjy1dFyFns93G1PkKceu67FTGfiTCgJbSI3DYMTabStpENTRLTM80FwNtEtOzvMueTxDXjS4EEevchK+SLmuUf9B19RhxCuc2idU6Z7say58oZdeJM7x0fJddymiN5l0Ijkn00qBprDgpo6OIi8xt6apNGDHStUhrBTZ9QphPrRLnWBpEdllXzFoaGF0IgrIG0CB6oEH0QIPogQZ7//xDB+zB+ByMsYp/CQZmzo1z/v7xu9bBGBsKgnNeUtwKOOhhjNWip/B4/ZP0kPXggKwHGgut5/uWsW8Pr8Wt57TfqOTJZn9K1vng/b5lba/9IzBds68kxzLreb5nd/fbm8e30tZz2m/kGJz2m0QE+emR00EI1eRPp0qNdAq5NIy3nreH2+3zx8vd7dOvCtYjp+hsUl/Z+eB9tyDBhnYzmUIWlljP69PN/cv7x9vDbS7/BmM9I5zDLlTRtchnPgus59fjt7vv4iSTf0OOPUqRzf401KPHYz1vD7dqhFge/xZlPfWcm+RmqEhP0LNJqrL4t7jYk2w0FtGjc2PcrB97DIeWyb/Fxp7y1tO35vIZaeX2cme87rw+3bDtc1HrUUg1VePpUa84WvSDe+/JfVDWwADl3KBB1gMNsh5ocM5nxWyyHihwzh0lZKpn48B0bnUnyxcWtwJEDzSIHmgQPdCIpidPSt/WxndLl18gqRNTuq6T1HE3zFMQs7XxClcS7fRkBnEXS9e1UqLuhnkqLrY2HtlZ+h/j3Lyl61oFBa+ataynuLHOZbtL19XKcX49K8WeevQESteA9FRybpXoCZeukekpHAwqxJ7LpWvo2FN0aVB65RZVusZbuaUvJ9va+G6VfO+JLF0jvfcUAWUNDBA90CB6oEH0QIPbxey62nxhcSvAncVsAg7IueGC6IEG0QMNogcaC+np23zVhCEwXrV3KMwTPWIE4JI6fcvats3HT4geJTVZRtbfWXNntiMrjZUSFXq0/dDn4yeOnmQDssB6bIlYBYVhYifzZPGKnslMMyDL6Jm7NrBynGQnszZe2TORaVRYQE/fMuXKqm6c97ScR+fC1eXq1mPpAkaPtJ2c+uDGHlsXqNiTZ3wc2rhvlF65jVDDromc3BzSyk25XoE8XzcAfu9RbzlyIODee3KDsgYGiB5oED3QIHqgwamYjQxOxWxwkHPDBdEDDaIHGkQPNBbQk2OPgK3NZdnpFLhEj/VjlbBJnUw7bGxt3DfyZB399FjFbJVjREyJZtTA0MZ9ozQ9I9zFbLzdcYXYAaZHaSAuIpXjEOhJH3oWluOEDg6PRvTUth4r0iDR89/HHgcZQLHnv1u5jQh9Uwdq5TbpBfPek2Q8FsUe2plNWQMTRA80iB5oED3Q4FTMRganYjY4yLnhguiBBtEDDaIHGtH0lNh9dSmpY1SWr1YiQI8jYSMvISZ1iuSsg9I3s6RxijniE6ftjpGnKgsssqNYKdH69Oz3ckt4327a9np9YpybKh9A78wGoOc0bdnv282+T6BPBD1qo6/3Zw0AynFG7Mnlay/QI2eysfU2uThNrPpaTtvq397Bo6e69Yz7j0dXkp8evQKpnWNunIegRywRUv34RYCeeXV40H8KBbGYDUKPCgJZ6XEU6yUFmMVsEHqS6uMT5/yZcez3niKgrIEBogcaRA80iB5ocCpmI4NTMRsZwzCw2lOEEMJf6uWKHIVs4VIAAAAASUVORK5CYII=" alt="" />

求从A地出发到各个地方的距离。

有经验的人一看，就知道题目关于树形查询的。SQL SERVER 2005数据库没有提供树形查询相关的函数，

因此，可以通过CTE递归实现。

题目比较简单，就不写什么分析思路了，直接看代码实现。

　　1.建表，导入测试数据

CREATE TABLE bus

    (

      dstart VARCHAR(4) ,

      dend VARCHAR(4) ,

      distance INT

    );

INSERT  INTO bus

VALUES  ( 'A', 'B', 120 );

INSERT  INTO bus

VALUES  ( 'B', 'C', 200 );

INSERT  INTO bus

VALUES  ( 'A', 'D', 150 );

INSERT  INTO bus

VALUES  ( 'D', 'M', 300 );

INSERT  INTO bus

VALUES  ( 'C', 'E', 180 );

INSERT  INTO bus

VALUES  ( 'F', 'M', 260 );

　　2.实现

WITH    x0 ( dstart, dend, way, distance )

          AS ( SELECT   dstart ,

                        dend ,

                        CONVERT(VARCHAR (20), dstart + '-' + dend) AS way ,

                        distance

               FROM     bus

               WHERE    dstart = 'A'

               UNION ALL

               SELECT   bus .dstart ,

                        bus.dend ,

                        CONVERT(VARCHAR (20), x0. way + '-' + bus .dend) AS way ,--路径

                        bus.distance + x0.distance AS distance --距离

               FROM     bus ,

                        x0

               WHERE    bus .dstart = x0 .dend

             )

    SELECT  way ,

            distance

    FROM    x0

　　3.效果

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIoAAABgCAIAAABNOn3XAAAFVUlEQVR4nO1czWrjMBCeh2oW7HcJoQf7QQoLPZk8RWHZ7MmQZ+ih0KshL9BLwN3rGrwHy7Ikj/yT2tZMOh+iOIpIZvR5RvJ8UeH3n1wa2QaVgY/PfxVbsDbeB6GHNIQe0hB6SON70FNkUZQVGxqzFL4TPShJtJn7TvTMfYsABunJE4AkV1f6IsqKKk9AI8mNgXrE5kDoKbJIW2lEj+6OslxdRllRuU41zGWJ0VNV5ijV033L8n4PR0/rUJ6YFhf9IUUWuURuDcz41pTmllHGtxY27w9kvCKLTA6irDA/0/5QY8ySGElujSt5EmV5FqmrovUdzJtGOR2MnZ7x5mRZa48yvXPEHIZFW+8T7G8BEwvzM7b25EmUZUlz07UcWQlMW5wnkOSavu0xmR4FlRG6zp5TU+lZ8X4c3RrkSXtL6AxnxUjnVJ6skn6nYiC5FVnURYN6Uakpz016vHlrMLmZi9DCVI3SY2fw9tutmG59CLUpaDC0NYgi8943tgZ6Q6AWVssphJ4K2Rp0PVtvDWYh3LJTVRXzpwIfFqIncGKrKqGHOFgb74PQQxrw8flPGtkGpQ0AYP33zmAlt7IsP65/mbayLOv7AkIPAASf6NsaAISez4Uh0UMaEj2kMTN6fj0C/Hh66/W/PT90xRBsAInoOaeQno0XCm1fvyc45kXPywH2h8eHn+8YPY8v+nr3/Eorei7H2Jr4yzF2Ly/HOD5eVI+6Co5Z0fP+tHt8uZ72/dk36bme9t013ejRUGwYb+LjQmBO9Lw9PxxOH9f3p10vfVGPngbotKvoMUOGTvjMiJ7Xnz/2v9SFm98Yrj0NLscY4uOl5kOPJ3ren3aGYrt7fr3+fTkAAMDhRCS5zY0ezU3NiB7ceZXZNFV2iFj0YNmPXvSY3Dhv8lt7nITm5jdn7aEfPefU3T5z3rmd9jAQLs7aA80SRTl6ukccAAAVReyfe4i3b181oN2k5ka6SfSQbvcZPaZ0KtFDCmVZIgIwBU1alOwGbnILfcfcDtbGoxB6SEPoIQ2hhzRm0nNOAbB6lFYjg9ZExugxS51mWUd5xKOoMzD6nEKapgg/VhXRUIq3hd94V8xGqtJcSqL+wZdjnJ7rM8KP41EgB2dET99CLoKCd2zDDjr3bl8YB+fRY6c2NnKcb6hiB7Xe7Qrj4Ax6zil0qYzVbw08I+3VPz5e9FrqOFfXDKLHQGM7c3p07KgXtv3s1h4DzH5IhY4bmX/qO7cG3bQbJrZpjvPOrUvVCg4F/J57uqcc7Rj75x7iYG08CqGHNIQe0hB6SKPsi9mhTbodrI1HUaJitoAOJLnRhdBDGkIPaQg9pDGTnkliNq6mblDyGTBeF2wQNZvXyeyB0UNitl+ItA869U/WLAav8V3JUJXdGJ/M9g8eFLO9Mv523k5Ibn4pkbWgUNdjYrYveja8F8fpQbSOO1FLx8Rs39pDhp7GRoQcXiezPSPHxOxeclPvxjEReuq6dsKH5clsfOComG15HSa7TdlYa2u4nsxGx42L2dZvlLz/PiDIzg3Zp/E9mY0N+6qYTee5xz2EjXYSiR2pGhCH0EMaQg9pCD2kUYqYTRmliNnEIcmNLoQe0hB6SEPoIY2Z9KwhZtsFoa+UVPzGTzqHzb6os4qYvVwFcpCesQIo55Jog3XE7A3omXIOm7WgUNeridnb0GOntr74xlyOW0/Mdtee229cr/ETzmFzp2dhMdt/qvtLzoyO8ZHBnJ71xOwQ9Nzb2rOimL0+PZPOYXPeua0pZveee26em4HomXIOm/1zD3GwNh6F0EMaQg9pCD2kUYqYTRmliNmUUdf1fxxTT/9YzhOTAAAAAElFTkSuQmCC" alt="" />

看到这题目，刚好做下练手，并分享给大家。大家若有更好的实现方式，欢迎分享，一起学习。

巴特西

计算从A地出发到各个地方的路径及距离

最新文章

热门文章