Ну да, конечно, в MySQL, нет ничего подобного и уже наверное не будет, иначе Oracle сделал бы этот функционал много лет назад.
Ставим вместо MySQL MariaDB и получаем обобщенные выражения и рекурсивные в том числе, оконные функции, обработку строки регекспом и тд.
PHP и не заметит подмены.
Для примера, решаем задачу, генерация «чистая», никаких лишних строк.
<![CDATA[
/*
* календарь на месяц, по умолчанию текущий - "select 0 monthShift from dual"
* начало недели с понедельника
* с отображением дней предыдущего и следующего месяцев в неполных неделях
*/
with recursive gen50 as (
select -10 rn
union all
select rn+1 from gen50 where rn<40 /* генерируем 50 строк */
), calend as (
select *, substr(dds, -2) ddH from (
select *, min(ttrimTop) over () trimTop, max(ttrimBottom) over () trimBottom from (
select *, first_value(rn) over (partition by curMonth order by weekdayIdx asc, rn desc) ttrimTop,
first_value(rn) over (partition by curMonth order by weekdayIdx desc, rn asc) ttrimBottom,
min(case when ld = dds and weekdayIdx = 6 then rn else 99 end) over() ttrimBottom2,
max(case when fd = dds and weekdayIdx = 0 then rn else -99 end) over() ttrimTop2
from (
select *, case when dds < fd then -1 when dds > ld then 0 else 1 end curMonth, dds = current_date curDay, weekday(dds) weekdayIdx from (
select *, dd.fd + interval (gen50.rn) day as dds from gen50
cross join (select last_day(now() + interval monthShift month ) ld, last_day(now() + interval monthShift month ) + interval 1 day - interval 1 month as fd from (select 0 monthShift from dual) dd) dd
) tt ) tt ) tt ) tt
where rn between trimTop and trimBottom /* обрезать по начальной и конечной неделе */
and rn between ttrimTop2 and ttrimBottom2 /* если число месяца на границе недели, то нужно "отрезать" раньше */
)
select * from calend
order by rn;
]]>
<![CDATA[
+----+------------+------------+------------+----------+--------+------------+----------+-------------+--------------+-----------+---------+------------+------+
| rn | ld | fd | dds | curMonth | curDay | weekdayIdx | ttrimTop | ttrimBottom | ttrimBottom2 | ttrimTop2 | trimTop | trimBottom | ddH |
+----+------------+------------+------------+----------+--------+------------+----------+-------------+--------------+-----------+---------+------------+------+
| -6 | 2017-10-31 | 2017-10-01 | 2017-09-25 | -1 | 0 | 0 | -6 | -7 | 99 | -99 | -6 | 35 | 25 |
| -5 | 2017-10-31 | 2017-10-01 | 2017-09-26 | -1 | 0 | 1 | -6 | -7 | 99 | -99 | -6 | 35 | 26 |
| -4 | 2017-10-31 | 2017-10-01 | 2017-09-27 | -1 | 0 | 2 | -6 | -7 | 99 | -99 | -6 | 35 | 27 |
| -3 | 2017-10-31 | 2017-10-01 | 2017-09-28 | -1 | 0 | 3 | -6 | -7 | 99 | -99 | -6 | 35 | 28 |
| -2 | 2017-10-31 | 2017-10-01 | 2017-09-29 | -1 | 0 | 4 | -6 | -7 | 99 | -99 | -6 | 35 | 29 |
| -1 | 2017-10-31 | 2017-10-01 | 2017-09-30 | -1 | 0 | 5 | -6 | -7 | 99 | -99 | -6 | 35 | 30 |
| 0 | 2017-10-31 | 2017-10-01 | 2017-10-01 | 1 | 0 | 6 | 29 | 0 | 99 | -99 | -6 | 35 | 01 |
| 1 | 2017-10-31 | 2017-10-01 | 2017-10-02 | 1 | 0 | 0 | 29 | 0 | 99 | -99 | -6 | 35 | 02 |
| 2 | 2017-10-31 | 2017-10-01 | 2017-10-03 | 1 | 0 | 1 | 29 | 0 | 99 | -99 | -6 | 35 | 03 |
| 3 | 2017-10-31 | 2017-10-01 | 2017-10-04 | 1 | 0 | 2 | 29 | 0 | 99 | -99 | -6 | 35 | 04 |
| 4 | 2017-10-31 | 2017-10-01 | 2017-10-05 | 1 | 0 | 3 | 29 | 0 | 99 | -99 | -6 | 35 | 05 |
| 5 | 2017-10-31 | 2017-10-01 | 2017-10-06 | 1 | 0 | 4 | 29 | 0 | 99 | -99 | -6 | 35 | 06 |
| 6 | 2017-10-31 | 2017-10-01 | 2017-10-07 | 1 | 0 | 5 | 29 | 0 | 99 | -99 | -6 | 35 | 07 |
| 7 | 2017-10-31 | 2017-10-01 | 2017-10-08 | 1 | 0 | 6 | 29 | 0 | 99 | -99 | -6 | 35 | 08 |
| 8 | 2017-10-31 | 2017-10-01 | 2017-10-09 | 1 | 1 | 0 | 29 | 0 | 99 | -99 | -6 | 35 | 09 |
| 9 | 2017-10-31 | 2017-10-01 | 2017-10-10 | 1 | 0 | 1 | 29 | 0 | 99 | -99 | -6 | 35 | 10 |
| 10 | 2017-10-31 | 2017-10-01 | 2017-10-11 | 1 | 0 | 2 | 29 | 0 | 99 | -99 | -6 | 35 | 11 |
| 11 | 2017-10-31 | 2017-10-01 | 2017-10-12 | 1 | 0 | 3 | 29 | 0 | 99 | -99 | -6 | 35 | 12 |
| 12 | 2017-10-31 | 2017-10-01 | 2017-10-13 | 1 | 0 | 4 | 29 | 0 | 99 | -99 | -6 | 35 | 13 |
| 13 | 2017-10-31 | 2017-10-01 | 2017-10-14 | 1 | 0 | 5 | 29 | 0 | 99 | -99 | -6 | 35 | 14 |
| 14 | 2017-10-31 | 2017-10-01 | 2017-10-15 | 1 | 0 | 6 | 29 | 0 | 99 | -99 | -6 | 35 | 15 |
| 15 | 2017-10-31 | 2017-10-01 | 2017-10-16 | 1 | 0 | 0 | 29 | 0 | 99 | -99 | -6 | 35 | 16 |
| 16 | 2017-10-31 | 2017-10-01 | 2017-10-17 | 1 | 0 | 1 | 29 | 0 | 99 | -99 | -6 | 35 | 17 |
| 17 | 2017-10-31 | 2017-10-01 | 2017-10-18 | 1 | 0 | 2 | 29 | 0 | 99 | -99 | -6 | 35 | 18 |
| 18 | 2017-10-31 | 2017-10-01 | 2017-10-19 | 1 | 0 | 3 | 29 | 0 | 99 | -99 | -6 | 35 | 19 |
| 19 | 2017-10-31 | 2017-10-01 | 2017-10-20 | 1 | 0 | 4 | 29 | 0 | 99 | -99 | -6 | 35 | 20 |
| 20 | 2017-10-31 | 2017-10-01 | 2017-10-21 | 1 | 0 | 5 | 29 | 0 | 99 | -99 | -6 | 35 | 21 |
| 21 | 2017-10-31 | 2017-10-01 | 2017-10-22 | 1 | 0 | 6 | 29 | 0 | 99 | -99 | -6 | 35 | 22 |
| 22 | 2017-10-31 | 2017-10-01 | 2017-10-23 | 1 | 0 | 0 | 29 | 0 | 99 | -99 | -6 | 35 | 23 |
| 23 | 2017-10-31 | 2017-10-01 | 2017-10-24 | 1 | 0 | 1 | 29 | 0 | 99 | -99 | -6 | 35 | 24 |
| 24 | 2017-10-31 | 2017-10-01 | 2017-10-25 | 1 | 0 | 2 | 29 | 0 | 99 | -99 | -6 | 35 | 25 |
| 25 | 2017-10-31 | 2017-10-01 | 2017-10-26 | 1 | 0 | 3 | 29 | 0 | 99 | -99 | -6 | 35 | 26 |
| 26 | 2017-10-31 | 2017-10-01 | 2017-10-27 | 1 | 0 | 4 | 29 | 0 | 99 | -99 | -6 | 35 | 27 |
| 27 | 2017-10-31 | 2017-10-01 | 2017-10-28 | 1 | 0 | 5 | 29 | 0 | 99 | -99 | -6 | 35 | 28 |
| 28 | 2017-10-31 | 2017-10-01 | 2017-10-29 | 1 | 0 | 6 | 29 | 0 | 99 | -99 | -6 | 35 | 29 |
| 29 | 2017-10-31 | 2017-10-01 | 2017-10-30 | 1 | 0 | 0 | 29 | 0 | 99 | -99 | -6 | 35 | 30 |
| 30 | 2017-10-31 | 2017-10-01 | 2017-10-31 | 1 | 0 | 1 | 29 | 0 | 99 | -99 | -6 | 35 | 31 |
| 31 | 2017-10-31 | 2017-10-01 | 2017-11-01 | 0 | 0 | 2 | 36 | 35 | 99 | -99 | -6 | 35 | 01 |
| 32 | 2017-10-31 | 2017-10-01 | 2017-11-02 | 0 | 0 | 3 | 36 | 35 | 99 | -99 | -6 | 35 | 02 |
| 33 | 2017-10-31 | 2017-10-01 | 2017-11-03 | 0 | 0 | 4 | 36 | 35 | 99 | -99 | -6 | 35 | 03 |
| 34 | 2017-10-31 | 2017-10-01 | 2017-11-04 | 0 | 0 | 5 | 36 | 35 | 99 | -99 | -6 | 35 | 04 |
| 35 | 2017-10-31 | 2017-10-01 | 2017-11-05 | 0 | 0 | 6 | 36 | 35 | 99 | -99 | -6 | 35 | 05 |
+----+------------+------------+------------+----------+--------+------------+----------+-------------+--------------+-----------+---------+------------+------+
42 rows in set (0.00 sec)
]]>
Добавим к CTE еще пару запросов, что-то вроде этого,
<![CDATA[
, aa as (
SELECT dd.dds dds2, GROUP_CONCAT(CONCAT_WS(CHAR(9), a.id, a.publish_date IS NOT NULL, a.publish_date IS NULL AND (a.for_date < now()), a.publish_date IS NULL AND (a.for_date > now()) ) SEPARATOR '\n ') aas
FROM dd
JOIN articles a WHERE a.deleted IS NULL
AND a.for_date BETWEEN dds AND dds + INTERVAL 1 DAY - INTERVAL 1 SECOND
AND (a.responsible = $user OR $user = -1)
GROUP BY dd.dds
)
]]>
и в итоге, будет как-то так.
