我有两个表:ORDER和ORDERFiltered ORDER如下所示:
OrderNO. Item Size Quantity
1111111 ITEM22 28 3
2222222 ITEM18 22 4
3333333 ITEM22 26 3
1111111 ITEM22 22 1
1111111 ITEM22 26 2
如何将值复制到第二个表（ORDERFiltered），该表如下所示:
OrderNO: Item 22 26 28
1111111 ITEM22 1 2 3
2222222 ITEM18 4 0 0
3333333 ITEM22 0 3 0
条件聚合可用于此操作（又称pivot）
drop table if exists t;
create table t
(iOrderNO int , Item varchar(10), Size int, Quantity int);
insert into t values
(1111111 , 'ITEM22' , 28, 3),
(2222222 , 'ITEM18' , 22, 4),
(3333333 , 'ITEM22' , 26, 3),
(1111111 , 'ITEM22' , 22, 1),
(1111111 , 'ITEM22' , 26, 2);
select iorderno,item,
max(case when size = 22 then quantity else 0 end) as '22',
max(case when size = 26 then quantity else 0 end) as '26',
max(case when size = 28 then quantity else 0 end) as '28'
from t
group by iorderno,item;
+----------+--------+------+------+------+
| iorderno | item | 22 | 26 | 28 |
+----------+--------+------+------+------+
| 1111111 | ITEM22 | 1 | 2 | 3 |
| 2222222 | ITEM18 | 4 | 0 | 0 |
| 3333333 | ITEM22 | 0 | 3 | 0 |
+----------+--------+------+------+------+
3 rows in set (0.001 sec)
