难写的SQL

Question

前两天参加一个面试遇到了一个难写的sql,有个goods表,如下：id             quantity                    Type
1               50                          出货
2               70                          进货
3               25                          出货
2               50                          出货
1               20                          出货
3               50                          进货
把它转化成这样的形式打印出来：
id             进货               出货           总量增加
1              0                  70               -70
2              70                 50                20
3              50                 25                25不用写PL/SQL，希望有高手用sql写出来！

Answer 1

行列转换问题
select id,
  sum(decode(type,'进货',quantity,0))进货,
  sum(decode(type,'出货',quantity,0))出货,
  sum(decode(type,'进货',quantity,'出货',quantity*-1,0))总量增加
from goods
group by id