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
;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *