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;
コメントを投稿するにはログインしてください。