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;