求数据重新排列问题的sql语句?(100分)

如下查询表 a 表<br>&nbsp;品名 &nbsp;回收数量 &nbsp;总多发出数量<br>&nbsp;a1 &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 63<br>&nbsp;a1 &nbsp; 8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;63<br>&nbsp; a1 &nbsp; 25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;63<br>&nbsp; a1 &nbsp; 40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;63<br>&nbsp; a2 &nbsp; 15 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40<br>&nbsp; a2 &nbsp; 30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40<br>&nbsp; a2 &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40<br><br>请问如何重新排列成如下格式<br>&nbsp;品名 &nbsp;多发出数量 &nbsp;<br>&nbsp;a1 &nbsp; 10 &nbsp; &nbsp; <br>&nbsp;a1 &nbsp; 8 &nbsp; &nbsp; <br>&nbsp;a1 &nbsp; 25 &nbsp; &nbsp; <br>&nbsp;a1 &nbsp; 20<br>&nbsp;a2 &nbsp; 15 &nbsp; &nbsp; <br>&nbsp;a2 &nbsp; 25

select b.*,a.* from returnshoudiandetail b, (select a1, hppinggkid from (<br>select -sum(jianquantity) as a1 ,hppinggkid from ( select HPPINGGKID,JIANQUANTITY<br>from chuzhudandetail where hongtongid=1<br>union all select HPPINGGKID,-JIANQUANTITY<br>from returnshoudiandetail where hongtongid=1 ) group by hppinggkid ))a<br>where a.hppinggkid=b.hppinggkid and b.hongtongid=1 and b.jianquantity&lt;a.a1<br><br>这里就是两个表汇总呀<br>JIANQUANTITY 数量<br>hppinggkid &nbsp;品名<br>chuzhudandetail 发出表<br>returnshoudiandetail 回收表

chuzhudandetail 发出表 记录发出数量<br>returnshoudiandetail 回收表 &nbsp;记录回收数量<br>JIANQUANTITY 数量字段<br>hppinggkid &nbsp;品名字段<br><br>现在是如何汇总出回收表中多发出的记录?.<br>如下格式<br>品名 &nbsp;多交回数量 &nbsp; &nbsp;退回数量 &nbsp; 回收单号 &nbsp; 原回收日期

我想这个问题是算法问题,统计时好象有点随机性.看样要学的东西还真多