提问者:小点点

mysql如果值=0复制到


我有两个表: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

共1个答案

匿名用户

条件聚合可用于此操作(又称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)