sql同时查询出明细和汇总

做报表（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_QTY
from
    ((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_QTY
from
    ((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.BILLID
left 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_no
from
    (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_id
order by dtl.location_code , dtl.material_code , dtl.wave_order

 

 

end。