您当前的位置: A表的结构是：
barcode s1 s2 s3 s4 s5
1009823 0 0 1 0 2
1009824 3 0 1 0 3
1009825 -1 2 0 3 0
变换成B表：
barcode size quantity requantity
1009823 s3 1 0
1009823 s4 2 0
1009824 s1 3 0
1009824 s3 1 0
1009824 s5 3 0
1009825 s1 0 -1
1009825 s2 2 0
1009825 s4 3 0
其中barcode是字符型的，S1--S5是int型的，正值记录在quantity中，负值记录在requantity中
怎么编写SQL语句啊 多谢咯 急求！！
------解决方案--------------------
- SQL code
select barcode,'s1' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union all select barcode,'s2' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union all select barcode,'s3' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union all select barcode,'s4' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union all select barcode,'s5' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a
------解决方案--------------------
- SQL code
select t1.* from ( select barcode,'s1' as size,case when s1>0 then s1 else 0 end as quantity, case when s1<0 then s1 else 0 end as requantity from a union all select barcode,'s2' as size,case when s2>0 then s2 else 0 end as quantity, case when s2<0 then s2 else 0 end as requantity from a union all select barcode,'s3' as size,case when s3>0 then s3 else 0 end as quantity, case when s3<0 then s3 else 0 end as requantity from a union all select barcode,'s4' as size,case when s4>0 then s4 else 0 end as quantity, case when s4<0 then s4 else 0 end as requantity from a union all select barcode,'s5' as size,case when s5>0 then s5 else 0 end as quantity, case when s5<0 then s5 else 0 end as requantity from a ) t1 order by t1.barcode