基于扫描算法的企业配送路线优化

1、/* 基于扫描算法的企业配送路线优化*/DECLARE temp0 TABLE (corpid INT ,Tcount DECIMAL(19 ,4)DECLARE temp1 TABLE (corpid INT ,Fcorpid INT ,Tdistance DECIMAL(19 ,4)DECLARE temp2 TABLE ( corpid INT ,Fcorpid INT ,Tdistance DECIMAL(19 ,4) ,Tdistance1 DECIMAL(19 ,4) ,Tcount DECIMAL(19 ,4) ,Tcount1 DECIMAL(19 ,4) ,Tcorner D

2、ECIMAL(19 ,4) )DECLARE temp3 TABLE (CarNum INT ,Tload DECIMAL(19 ,4) ,Tdistance DECIMAL(19 ,4) DECLARE temp4 TABLE ( CarNum INT ,corpid INT ,Fcorpid INT ,Tcount DECIMAL(19 ,4) ,Tdistance DECIMAL(19 ,4) ,Tcorner DECIMAL(19 ,4) ,num INT ) DECLARE count INT ,count1 INT ,count2 INT ,corpid INT ,Fcorpid

3、INT ,Rcorpid INT ,Hcorpid INT ,CarNum INT ,Tload DECIMAL(19 ,4) ,Tcount DECIMAL(19 ,4) ,Tdistance DECIMAL(19 ,4) ,Tdistance1 DECIMAL(19 ,4) ,num INT ,Tcounsum DECIMAL(19 ,4) ,Tloadsum DECIMAL(19 ,4) ,Tcorner DECIMAL(19 ,4)SET count = 0SET count1 = 50- 门店及配送数量WHILE count<=count1BEGIN INSERT INTO t

4、emp0 SELECT count ,30 + RAND()*30-RAND()*100 - 门店配送数量 SET count = count+1END- 车辆信息SET count2 = 1WHILE count2<=10BEGIN INSERT INTO temp3 SELECT count2 ,300 -负载 ,300 -里程 SET count2 = count2+1END-店间距WHILE count>=0BEGIN SET count1 = 50 WHILE count1>=0 BEGIN INSERT INTO temp1 SELECT t1.corpid ,t

5、2.corpid ,20 + RAND()*40 -RAND()*100 -店间距 FROM temp0 t1 ,temp0 t2 WHERE t1.corpid<t2.corpid -过滤重复店间距 AND t1.corpid = count AND t2.corpid = count1 SET count1 = count1 - 1 END SET count = count - 1END-店间距合并角度表INSERT INTO temp2SELECT t1.Fcorpid ,t2.Fcorpid ,t1.Tdistance+t2.Tdistance- t3.Tdistance -节

6、约里程数 ,t1.Tdistance+t2.Tdistance+t3.Tdistance -里程增量 ,t0.Tcount ,t00.Tcount ,(t1.Tdistance * t1.Tdistance + t2.Tdistance * t2.Tdistance - t3.Tdistance * t3.Tdistance )/(2 * t1.Tdistance * t2.Tdistance)-求两个门店到中心角度的cosFROM temp1 t1 ,temp1 t2 ,temp1 t3 ,temp0 t0 ,temp0 t00WHERE t1.corpid = 0 AND t2.corpi

7、d = 0 AND t1.Fcorpid<t2.Fcorpid AND t1.Fcorpid = t3.corpid AND t2.Fcorpid = t3.Fcorpid AND t1.Tdistance + t2.Tdistance - t3.Tdistance>0 -保证三点成三角形 AND t0.corpid = t1.Fcorpid AND t00.corpid = t2.Fcorpid AND (t1.Tdistance * t1.Tdistance + t2.Tdistance * t2.Tdistance - t3.Tdistance * t3.Tdistance

8、)/(2 * t1.Tdistance * t2.Tdistance) >= -1 - <=180°三点间直线 AND (t1.Tdistance * t1.Tdistance + t2.Tdistance * t2.Tdistance - t3.Tdistance * t3.Tdistance )/(2 * t1.Tdistance * t2.Tdistance) < 1 - > 0° ORDER BY (t1.Tdistance * t1.Tdistance + t2.Tdistance * t2.Tdistance - t3.Tdistance

9、 * t3.Tdistance )/(2 * t1.Tdistance * t2.Tdistance) DESC -判断配送总量是否大于车载重总量SELECT Tcounsum = SUM(t0.Tcount)FROM temp0 t0SELECT Tloadsum = SUM(t3.Tload)FROM temp3 t3IF Tcounsum>TloadsumBEGIN SELECT '超出运载范围'ENDSET Tcounsum = 0SET Tloadsum = 0-从最短里程店往下合并SELECT TOP 1 corpid = t.corpid ,CarNum =

10、 t3.CarNumFROM temp1 t1 ,temp0 t ,temp3 t3WHERE t.Tcount < t3.Tload-过滤重复店间距 AND t1.corpid = 0 AND t1.Fcorpid = t.corpid AND t1.Tdistance < t3.TdistanceORDER BY t1.TdistanceSET Hcorpid = corpid -找出最短里程店的最小角门店（本例特殊方式，可更改获取起点方式）SELECT TOP 1 Fcorpid = t.Fcorpid,Tcorner = t.TcornerFROM temp2 t ,tem

11、p3 t3WHERE t.Tcount+t.Tcount1<t3.Tload -判断不超出负载 AND t3.CarNum = CarNum AND t.corpid = corpid AND t.Tdistance1 < t3.Tdistance -判断不超出里程WHILE ROWCOUNT>0BEGIN-获取当前线路最大编号 SET num = 0 SELECT num = ISNULL(MAX(t4.num) ,0)+1 FROM temp4 t4 WHERE t4.CarNum = CarNum -将门店信息插入线路配送表 INSERT INTO temp4 SELE

12、CT CarNum ,corpid ,Rcorpid ,t0.Tcount ,CASE WHEN ( SELECT COUNT(*) FROM temp4 t4 WHERE t4.CarNum = CarNum )=0 THEN t2.Tdistance ELSE t1.Tdistance END ,Tcorner ,num FROM temp0 t0 ,temp1 t1 ,temp1 t2 WHERE t0.corpid = corpid AND t1.corpid = t0.corpid AND t1.Fcorpid = Fcorpid AND t2.corpid = 0 AND t2.F

13、corpid = t0.corpid AND t0.corpid NOT IN (SELECT t4.corpid FROM temp4 t4) UNION ALL SELECT CarNum ,Fcorpid ,Rcorpid ,t0.Tcount ,t1.Tdistance ,Tcorner ,CASE WHEN ( SELECT COUNT(*) FROM temp4 t4 WHERE t4.CarNum = CarNum )=0 THEN num+1 ELSE num END FROM temp0 t0 ,temp1 t1 WHERE t0.corpid = Fcorpid AND t

14、0.corpid = t1.Fcorpid AND t1.corpid = corpid AND t0.corpid NOT IN (SELECT t4.corpid FROM temp4 t4) -获取当前线路的尾节点编号 SET num = 0 SELECT num = ISNULL(MAX(t4.num) ,0) FROM temp4 t4 WHERE t4.CarNum = CarNum -线路的尾节点门店 SELECT Rcorpid = t4.corpid FROM temp4 t4 WHERE t4.CarNum = CarNum AND t4.num = num -删除已分派的

15、门店 DELETE temp2 WHERE corpid = corpid AND Fcorpid = Fcorpid -已分派线路长度汇总 SET Tdistance = 0 SELECT Tdistance = SUM(t4.Tdistance) FROM temp4 t4 WHERE t4.CarNum = CarNum -已分派线路负载汇总 SELECT Tload = SUM(t4.Tcount) FROM temp4 t4 WHERE t4.CarNum = CarNum -获取剩余的里程数和车辆负载 SELECT Tload = t3.Tload- ISNULL(Tload ,0

16、) ,Tdistance1 = t3.Tdistance FROM temp3 t3 WHERE t3.CarNum = CarNum -获取当前线路尾节点关联的最小角度的门店 SELECT TOP 1 corpid = t.corpid ,Fcorpid = t.Fcorpid ,Tcorner = t.Tcorner FROM temp2 t ,temp1 t1 ,temp1 t2 WHERE (t.corpid=Rcorpid OR t.Fcorpid=Rcorpid) AND t.corpid = t1.corpid AND t.Fcorpid = t1.Fcorpid AND ( C

17、ASE WHEN t.corpid=Rcorpid THEN t.Fcorpid WHEN t.Fcorpid=Rcorpid THEN t.corpid END ) = t2.Fcorpid AND t2.corpid = 0 AND ( CASE WHEN ( t.corpid IN (SELECT t4.corpid FROM temp4 t4 WHERE t4.CarNum = CarNum) ) THEN t.Tcount1 ELSE t.Tcount END )<ISNULL(Tload ,0) -判断增量不超出负载 AND Tdistance+t1.Tdistance+t2

18、.Tdistance<Tdistance1 -判断增量不超出运载里程 SET count = ROWCOUNT IF count=0 BEGIN SET Rcorpid = 0 -删除已分派的门店 DELETE temp2 WHERE corpid <> Hcorpid AND Fcorpid <> Hcorpid AND (corpid IN (SELECT DISTINCT t4.corpid FROM temp4 t4) OR Fcorpid IN (SELECT DISTINCT t4.corpid FROM temp4 t4) -找出最短里程店的最小角门

19、店SELECT TOP 1 CarNum = t3.CarNum, corpid = CASE WHEN t.corpid = Hcorpid THEN t.Fcorpid WHEN t.Fcorpid = Hcorpid THEN t.corpid END,Tcorner = t.TcornerFROM temp2 t ,temp3 t3WHERE t.Tcount+t.Tcount1<t3.Tload AND (t.corpid = Hcorpid OR t.Fcorpid = Hcorpid) AND t.Tdistance1 < t3.Tdistance AND t3.Ca

20、rNum NOT IN (SELECT DISTINCT t4.CarNum FROM temp4 t4) AND (CASE WHEN t.corpid = Hcorpid THEN t.Fcorpid WHEN t.Fcorpid = Hcorpid THEN t.corpid END) NOT IN (SELECT DISTINCT t4.corpid FROM temp4 t4) -找出起始店的最小角门店 SELECT TOP 1 Fcorpid = t.Fcorpid,Tcorner = t.TcornerFROM temp2 t ,temp3 t3WHERE t.Tcount+t.

21、Tcount1<t3.Tload AND t3.CarNum = CarNum AND t.corpid = corpid AND t.Tdistance1 < t3.Tdistance AND t3.CarNum NOT IN (SELECT DISTINCT t4.CarNum FROM temp4 t4) AND t.Fcorpid NOT IN (SELECT DISTINCT t4.corpid FROM temp4 t4) END -保证循环继续 SELECT TOP 1 corpid = t.corpid ,Fcorpid = t.Fcorpid FROM temp2

22、 t WHERE t.corpid = corpid AND t.Fcorpid = FcorpidEND-未分派的门店单独一个线路SELECT TOP 1 corpid = t.corpid ,CarNum = t3.CarNum ,Tcount = t.Tcount ,Tdistance = t1.TdistanceFROM temp0 t ,temp1 t1 ,( SELECT t3.CarNum ,t3.Tload FROM temp3 t3 WHERE t3.CarNum NOT IN (SELECT DISTINCT t4.CarNum FROM temp4 t4) ) t3WHERE t.corpid NOT IN (SELECT DISTINCT t4.corpid FROM temp4 t4) AND t.Tcount<