MariaDB update records or cteate table from CTE

CTE в марии это хорошо, но как сохранить результаты. В документации не нашел, аналогия с SQL Server не работает.

    update load0 l
        join 
         ( with t1 as (
             select row_number() over (partition by col1, substring(col2, 1, 15), col3,
              regexp_replace(regexp_substr(col4, '^[^|]*'),'[^а-я]',''),
               col6, regexp_replace(regexp_substr(col7, '^[^|]*'),'(http)*(s)*(www.)*[\/\:]',''), lower(col8), col9, col10 order by col11_id, col12_id) rn, l0.* from load0 l0
            )
                select * from t1
          ) t on (l.id=t.id)
            set l.client_number = t.rn
;
            create table col12 
            with t1 as (
            select col11, col12, count(*) cnt from load0
             group by col11, col12
            ), t2 as (
                select *, dense_rank() over (order by col11 desc) col11_id, dense_rank() over (partition by col11 order by col12) col12_id  from t1
            )
            , t3 as (
                select distinct col11, col11_id from t2
            )
                select col11_id, col12_id, col12 from t2
;

MariaDB(MySQL) recursive CTE and Window Functions

Ну да, конечно, в MySQL, нет ничего подобного и уже наверное не будет, иначе Oracle сделал бы этот функционал много лет назад. Ставим вместо MySQL MariaDB и получаем обобщенные выражения и рекурсивные в том числе, оконные функции, обработку строки регекспом и тд. PHP и не заметит подмены. Для примера, решаем задачу, генерация «чистая», никаких лишних строк.

/*
* календарь на месяц, по умолчанию текущий - "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;

+----+------------+------------+------------+----------+--------+------------+----------+-------------+--------------+-----------+---------+------------+------+
| 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 еще пару запросов, что-то вроде этого,

 , 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
 )
и в итоге, будет как-то так. calend

Век живи, век учись, вариант выделения дробной части

Задача, положить дробное число раздельно в два столбца, воспользуемся оператором взятия остатка от деления %, это Лёха подсказал
drop table #test17;

create table #test17(c1 int, c2 decimal(10,10) /*отдадим все 10 знаков на хранение дробной части*/);

insert into #test17
 select c,c from (select 12.34567 c) tt;
-- для MSSQL Arithmetic overflow error converting numeric to data type numeric.
-- The statement has been terminated.
-- или в MySQL Data truncation: Out of range value for column 'c2' at row 1

insert into #test17
 select c,c%1 from (select 12.34567 c) tt;

select * from #test17;

c1	c2
12	0.3456700000

для Оракла функция mod(c, d)