Hello. There is such a mssql query:
declare @start date = '2016-12-12'; -- Новая дата заезда declare @end date = '2016-12-20'; -- Новая дата выезда declare @today date = '2016-12-11'; update main set numbernomer = null where datestart > @today; declare @mainnumber int; while exists (select * from main where numbernomer is null) begin select top(1) @mainnumber = id from main where numbernomer is null order by datestart asc, datediff(day, datestart, dateend) desc; update m0 set numbernomer = m1.nomer from main m0 cross apply (SELECT top 1 a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, m0.dateend) * DATEDIFF(day, m.dateend, m0.datestart) <= 0 left join (select m2.numbernomer, max(m2.dateend) as dateend from main m2 group by m2.numbernomer) as m2 on a.nomer = m2.numbernomer WHERE a.type = 'lux' AND m.numbernomer IS NULL order by datediff(day, m2.dateend, m0.datestart), a.nomer ) as m1 where m0.id = @mainnumber; end; SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, @end) * DATEDIFF(day, m.dateend, @start) <= 0 WHERE a.type = 'lux' AND m.numbernomer IS NULL; I am trying to translate this query into mysql. I know that I need to change decale to set, remove the data type and replace top 1 with limit 1. all ... Tell me how to translate this query to mysql? What needs to be changed?
PS The request must be executed in this table: http://sqlfiddle.com/#!9/81225
