VirtualBox の CentOS7 に入れた MySQL 8 で CTE を試してみました。

あまり参考にはならず、利用効果少ないですが、

[ 利用例 ]

貼ってから気づいたのですが、プラグインの Simple Code Highlighter が対応してなく、with が太字になってないです。

with cnttbl (ld, dc, cnt)
as (
select
    d, dc, count(id) as cnt
from
    sch_export_source
group by
    ld, dc
)

select
    dc,
    pp.CD_CNTRY,
    c.CD_REGION,

    sum(Tomakomai) as Tomakomai,
    sum(Sendai) as Sendai,
    sum(Tokyo) as Tokyo,
    sum(Chiba) as Chiba,
    sum(Yokohama) as Yokohama,
    sum(Shimizu) as Shimizu,

    sum(Niigata) as Niigata,
    sum(Toyama) as Toyama,

    sum(Nagoya) as Nagoya,
    sum(Yokkaichi) as Yokkaichi,
    sum(Osaka) as Osaka,
    sum(Kobe) as Kobe,

    sum(Mizushima) as Mizushima,
    sum(Hiroshima) as Hiroshima,
    sum(Matsuyama) as Matsuyama,

    sum(Moji) as Moji,
    sum(Hakata) as Hakata,

    sum(Kagoshima) as Kagoshima,
    sum(Naha) as Naha

from
    (
    select
        dc,

        case when (ld = 'Tomakomai') then cnt else 0 end as Tomakomai,
        case when (ld = 'Sendai') then cnt else 0 end as Sendai,
        case when (ld = 'Tokyo') then cnt else 0 end as Tokyo,
        case when (ld = 'Chiba') then cnt else 0 end as Chiba,
        case when (ld = 'Yokohama') then cnt else 0 end as Yokohama,
        case when (ld = 'Shimizu') then cnt else 0 end as Shimizu,

        case when (ld = 'Niigata') then cnt else 0 end as Niigata,
        case when (ld = 'Toyama Shinko') then cnt else 0 end as Toyama,

        case when (ld = 'Nagoya') then cnt else 0 end as Nagoya,
        case when (ld = 'Yokkaichi') then cnt else 0 end as Yokkaichi,
        case when (ld = 'Osaka') then cnt else 0 end as Osaka,
        case when (ld = 'Kobe') then cnt else 0 end as Kobe,

        case when (ld = 'Mizushima') then cnt else 0 end as Mizushima,
        case when (ld = 'Hiroshima') then cnt else 0 end as Hiroshima,
        case when (ld = 'Matsuyama') then cnt else 0 end as Matsuyama,

        case when (ld = 'Moji') then cnt else 0 end as Moji,
        case when (ld = 'Hakata') then cnt else 0 end as Hakata,

        case when (ld = 'Kagoshima') then cnt else 0 end as Kagoshima,
        case when (ld = 'Naha') then cnt else 0 end as Naha

    from
        /*  この部分を CTE にしてみる */
        /*(
        select
            ld, dc, count(id) as cnt
        from
            sch_export_source
        group by
            ld, dc
        )a*/
        cnttbl a	
    ) b
    left outer join mst_ppcnv pp on (b.dc = pp.NM)
    left outer join mst_cntry c on (pp.CD_CNTRY = c.CD_CNTRY) 
group by
    dc, CD_CNTRY,
    CD_REGION
order by
  CD_CNTRY, dc;