1. 源表结构：
| order_id |
class |
count |
| 111101100002 |
3 |
0 |
| 111101100012 |
1 |
3 |
| 11110112002 |
1 |
2 |
| 111101100202 |
2 |
0 |
2. 目标表结构：
根据order_id分组，根据不同的class进行统计
| order_id |
count_1 |
count_2 |
|
count_3 |
count_4 |
|
count_5 |
count_6 |
|
count_7 |
count_8 |
|
count_9 |
count_10 |
| 111101100002 |
3 |
0 |
0 |
3 |
0 |
0 |
3 |
0 |
0 |
3 |
0 |
0 |
3 |
0 |
3. sql语句如下：
（1）分组统计，
（2）使用 COALESCE 返回第一个不为null的值，如果值为null，返回0
select order_id,
sum(COALESCE(cass class when 1 then count END,0)) as count_1,
sum(COALESCE(cass class when 2 then count END,0)) as count_2,
sum(COALESCE(cass class when 3 then count END,0)) as count_3,
sum(COALESCE(cass class when 4 then count END,0)) as count_4,
sum(COALESCE(cass class when 5 then count END,0)) as count_5,
sum(COALESCE(cass class when 6 then count END,0)) as count_6,
sum(COALESCE(cass class when 7 then count END,0)) as count_7,
sum(COALESCE(cass class when 8 then count END,0)) as count_8,
sum(COALESCE(cass class when 9 then count END,0)) as count_9,
sum(COALESCE(cass class when 10 then count END,0)) as count_10
from test_table group by order_id;
4. IF ELSE不太好用，可以用CASE WHEN THEN ELSE代替
比如 active_hour int 值分别为0~23
但是想要得到俩位的HH的小时值，需要把 0~9 转换为 00 ~ 09
select active_hour,case when active_hour > 9 then ''||active_hour else '0'||active_hour from t_active;
