转换成：
方法1: sum(case when...)
select
--case
--when month = '01' THEN sum_qty
--end as '01', --SqlParseException: Sql 1 Encountered "\'01\'" at line 4, column 12. Please check [end as '01',].
sum( -- sum要写在外层
case
when month = '01' THEN sum_qty -- 不可写成sum(sum_qty),会报错org.apache.calcite.runtime.CalciteContextException: Sql 1: From line 3, column 14 to line 3, column 18: Expression 'month' is not being grouped
end
) as `01`, -- 要用``不可用''
sum(
case
when month = '02' THEN sum_qty
end
) as `02`,
sum(
case
when month = '03' THEN sum_qty
end
) as `03`,
sum(
case
when month = '04' THEN sum_qty
end
) as `04`,
sum(
case
when month = '05' THEN sum_qty
end
) as `05`
from
(
select
date_format(order_day, 'MM') as month,
sum(qty) as sum_qty
from
(
select
11 as order_id,
'2022-01-02 ' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
union all
select
9 as order_id,
'2021-01-02' as order_day,
6 as qty
union all
select
8 as order_id,
'2021-02-06' as order_day,
7 as qty
) t1
group by
date_format(order_day, 'MM')
) t2;
2 方法2: sum(map[''])
select
sum(map_type ['01']) as `01`, -- 必须要带sum,不然就是多行了！！
sum(map_type ['02']) as `02`,
sum(map_type ['03']) as `03`,
sum(map_type ['04']) as `04`,
sum(map_type ['05']) as `05`
from(
select
map(month, sum_qty) as map_type
from
(
select
date_format(order_day, 'MM') as month,
sum(qty) as sum_qty
from
(
select
11 as order_id,
'2022-01-02' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
union all
select
9 as order_id,
'2021-01-02' as order_day,
6 as qty
union all
select
8 as order_id,
'2021-02-06' as order_day,
7 as qty
) t1
group by
date_format(order_day, 'MM')
) t2
) t3;
方法1不带sum的结果，与方法2不带sum的结果 相同，如下：
select
--case
--when month = '01' THEN sum_qty
--end as '01', --SqlParseException: Sql 1 Encountered "\'01\'" at line 4, column 12. Please check [end as '01',].
case
when month = '01' THEN sum_qty
end as `01`, --SqlParseException: Sql 1 Encountered "\'01\'" at line 4, column 12. Please check [end as '01',].
case
when month = '02' THEN sum_qty
end as `02`,
case
when month = '03' THEN sum_qty
end as `03`,
case
when month = '04' THEN sum_qty
end as `04`,
case
when month = '05' THEN sum_qty
end as `05`
from
(
select
date_format(order_day, 'MM') as month,
sum(qty) as sum_qty
from
(
select
11 as order_id,
'2022-01-02 ' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
union all
select
9 as order_id,
'2021-01-02' as order_day,
6 as qty
union all
select
8 as order_id,
'2021-02-06' as order_day,
7 as qty
) t1
group by
date_format(order_day, 'MM')
) t2;
select
map_type ['01'] as `01`,
map_type ['02'] as `02`,
map_type ['03'] as `03`,
map_type ['04'] as `04`,
map_type ['05'] as `05`
from(
select
map(month, sum_qty) as map_type
from
(
select
date_format(order_day, 'MM') as month,
sum(qty) as sum_qty
from
(
select
11 as order_id,
'2022-01-02' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
union all
select
9 as order_id,
'2021-01-02' as order_day,
6 as qty
union all
select
8 as order_id,
'2021-02-06' as order_day,
7 as qty
) t1
group by
date_format(order_day, 'MM')
) t2
) t3;
