Hello. I already asked the question: How to defragment the dates in the database? and a solution was proposed there, but as it turned out, there is an error in this decision. enter image description here This is how the base looks like initially. If you apply this code:

set @today:=date('2016-12-11'); update main M join ( select cid, nomer from ( select A.nomer, @cstart:=if(@cnum=A.nomer,@cstart,A.start), @cnum:=A.nomer, @cid:=(select M.id from main M,allnomer MT where MT.nomer=M.numbernomer and MT.type='lux' and M.datestart>@cstart and find_in_set(M.id,@used)=0 order by datestart limit 1 ) cid, @cstart:=(select dateend from main where id=@cid) dend, @used:=coalesce(concat(@used,',',@cid),@used) from ( select A.nomer, A.start from ( select A.nomer, (select coalesce(min(dateend),@today-interval 1 day) from main M where datestart<=@today and M.numbernomer=A.nomer) start from allnomer A where type='lux' ) A, main M, allnomer MT where MT.nomer=M.numbernomer and MT.type='lux' and M.datestart>A.start order by A.start desc, A.nomer ) A, (select @cid:=0,@cnum:=0,@cstart:=NULL,@used:='') Y ) X where cid is not null ) U on M.id=U.cid set M.numbernomer=U.nomer

That base will look like this: (it should look like this after defragmentation) enter image description here

But if you change set @today: = date ('2016-12-11'); at set @today: = date ('2016-12-05'); (or any date younger than 10), then defragmentation will be like this: enter image description here

Tell me how to fix it?

  • When all numbers are still free, the algorithm considers only one option, starting the filling with the minimum number. For an absolutely correct defragmentation, no algorithm that selects the first available reservation is suitable. It is necessary to consider all possible variants of armor rearrangements. This requires recursion, and MySQL is almost the only database that does not support recursive queries. And emulation of a recursion for the sake of several armor will generate millions of lines and will work very slowly. - Mike
  • although for the sake of fun, try to add to the receipt of the next armor in the chain (before limit 1) to sort the length of the armor that would take the longest one first, it can help. but all the same, there will surely be such combinations that will not be optimized effectively - Mike
  • @Mike the funny thing is that he does not touch the first dates. He could throw 10-11 upward. And about sorting. Should there be something of type order dateend - datestart? - firebear
  • In the previous question, you had your sample query, where the datediff() function was used just to return the difference of two dates. And what he will throw where the actual sorting and decides. There now stands only by the start date, it is impossible to predict which record with the same start dates it will take - Mike
  • one
    Doesn't it seem to you that the solution, the result of which depends on the parameter missing in the original problem, cannot be correct by definition? - Akina

0