你的位置:首页 > 数据库

[数据库]商品要求根据货号色值码分组横向展示尺码及库存


SQL:

select *,(A24+A26+A28+A29+A30+A31+A32+A33+A34+A35+A36+A37+A38+A39+A40+A41+A42+A43+A44) as Number from (select Tz.productno,Tz.color,Tz.warehouseid
, Sum((case ts.size when '24' then (ts.qty) else 0 end )) as A24
, Sum((case ts.size when '26' then (ts.qty) else 0 end )) as A26
, Sum((case ts.size when '28' then (ts.qty) else 0 end )) as A28
, Sum((case ts.size when '29' then (ts.qty) else 0 end )) as A29
, Sum((case ts.size when '30' then (ts.qty) else 0 end )) as A30
, Sum((case ts.size when '31' then (ts.qty) else 0 end )) as A31
, Sum((case ts.size when '32' then (ts.qty) else 0 end )) as A32
, Sum((case ts.size when '33' then (ts.qty) else 0 end )) as A33
, Sum((case ts.size when '34' then (ts.qty) else 0 end )) as A34
, Sum((case ts.size when '35' then (ts.qty) else 0 end )) as A35
, Sum((case ts.size when '36' then (ts.qty) else 0 end )) as A36
, Sum((case ts.size when '37' then (ts.qty) else 0 end )) as A37
, Sum((case ts.size when '38' then (ts.qty) else 0 end )) as A38
, Sum((case ts.size when '39' then (ts.qty) else 0 end )) as A39
, Sum((case ts.size when '40' then (ts.qty) else 0 end )) as A40
, Sum((case ts.size when '41' then (ts.qty) else 0 end )) as A41
, Sum((case ts.size when '42' then (ts.qty) else 0 end )) as A42
, Sum((case ts.size when '43' then (ts.qty) else 0 end )) as A43
, Sum((case ts.size when '44' then (ts.qty) else 0 end )) as A44
from tz_tq AS Tz LEFT JOIN tz_tq AS ts ON ts.id = Tz.id where 1=1
group by Tz.productno,Tz.color,Tz.warehouseid ) T

面对公司导出产品库存单需要尺码库存横向展示和汇总~   tz_tq为产品表,仅供参考。