做报表（ireport）时候要进行分组汇总，但是报表的汇总智能在 group footer 里面，写在group header里面没有值，于是打算从数据源里面做处理，实践出来有两种方式：
第一种：
| 储位 | 物料 | 数量 |
| 001 | ? | 100 |
| ? | A | 10 |
| ? | B | 90 |
| 002 | ? | 30 |
| ? | C | 29 |
| ? | A | 1 |
上面，通过两个查询union 和 order by 可以获得在每个明细之前或者之后，增加一个汇总行的结构，sql如下：
select A.BILLID, SUM_QTY, ONL_NOTICE_QTYfrom ((SELECT BILLID, ONL_NOTICE_QTY FROM WM_OUTBOUND_NOTICE_LINE WHERE BILLID = 14339) A LEFT JOIN (SELECT BILLID, SUM(ONL_NOTICE_QTY) SUM_QTY FROM WM_OUTBOUND_NOTICE_LINE WHERE BILLID = 14339 GROUP BY BILLID) B ON A.BILLID = B.BILLID)ORDER BY BILLID
?
第二种：
| A | 1 | 6 |
| ? | 2 | 6 |
| ? | 3 | 6 |
| B | 7 | 9 |
| ? | 2 | 9 |
| C | 5 | 5 |
?
也就是，增加一列来记录汇总数量，用join 实现，如果需要多个分组，就要多次join：
select A.BILLID, SUM_QTY, ONL_NOTICE_QTYfrom ((SELECT BILLID, ONL_NOTICE_QTY FROM WM_OUTBOUND_NOTICE_LINE) A LEFT JOIN (SELECT BILLID, SUM(ONL_NOTICE_QTY) SUM_QTY FROM WM_OUTBOUND_NOTICE_LINE GROUP BY BILLID) B ON A.BILLID = B.BILLIDleft join () c)ORDER BY BILLID
?项目中的实际sql是：
SELECT dtl.source_shop, dtl.wave_no, dtl.owner_name, dtl.carrier_name, dtl.material_wave_type_count, dtl.material_wave_sum_count, dtl.storeroom_name, dtl.storearea_name, dtl.location_code, dtl.location_name, dtl.material_code, dtl.material_name, dtl.material_barcode, dtl.material_spec, dtl.wave_order, dtl.material_loc_qty, grp_loc_mat.sum_loc_mat_qty, grp_loc.sum_loc_qty, dtl.order_no, dtl.express_nofrom (select onh.onh_source_shop source_shop, l.billdtlid, h.SH_WAVE_NO wave_no, 'xxx' owner_name, 'xxx' carrier_name, 'xxx' material_wave_type_count, 'xxx' material_wave_sum_count, 'xxx' storeroom_name, 'xxx' storearea_name, loc.code location_code, loc.name location_name, m.code material_code, m.name material_name, 'xxx' material_barcode, m.mat_spec material_spec, h.sh_wave_order wave_order, l.SL_SHIP_QTY material_loc_qty, onh.onh_external_order_no order_no, onh.onh_consign_no express_no, l.sl_location_id, l.sl_material_id from wm_ship_line l left join wm_ship_header h ON l.billid = h.billid left join wm_outbound_notice_header onh ON h.sh_notice_header_id = onh.billid left join cp_owner c ON c.id = h.sh_carrier_id left join cp_location loc ON loc.id = l.sl_location_id left join cp_material m ON m.id = l.sl_material_id where h.sh_notice_header_id in (1)) dtl left join (select l.sl_location_id, sum(l.SL_SHIP_QTY) sum_loc_qty from wm_ship_line l left join wm_ship_header h ON l.billid = h.billid where h.sh_notice_header_id in (1) group by l.sl_location_id) grp_loc ON dtl.sl_location_id = grp_loc.sl_location_id left join (select l.sl_location_id, l.sl_material_id, sum(l.SL_SHIP_QTY) sum_loc_mat_qty from wm_ship_line l left join wm_ship_header h ON l.billid = h.billid where h.sh_notice_header_id in (1) group by l.sl_location_id , l.sl_material_id) grp_loc_mat ON dtl.sl_location_id = grp_loc_mat.sl_location_id and dtl.sl_material_id = grp_loc_mat.sl_material_idorder by dtl.location_code , dtl.material_code , dtl.wave_order
?
?
end。
?
?