rollup是针对每一个组都要求和,我的目的是对总表进行求和 sql如下: SELECT f.organ_id, f.organ_name organ_name, COALESCE(SUM(B.PLAN_ORD * siu.um_size / siv.um_size), 0) ORD, SUM(sc.HEJI) FROM DIM_ITEM A, SD_ITEM_UM_VIEW SIV, SD_ITEM_UM_VIEW SIU, SD_PLAN_DAY_GZ B LEFT JOIN (SELECT organ_name, organ_id FROM pub_organ) F ON b.sale_center_id = F.organ_id full join (SELECT C.ITEM_ID, d.sale_center_id, sum(a.qty_sum) heji FROM SD_CO A, SD_CO_LINE B, SD_ITEM C, SD_ITEM_TOBACCO E, RM_CUST D WHERE A.CO_NUM = B.CO_NUM AND B.ITEM_ID = C.ITEM_ID AND B.ITEM_ID = E.ITEM_ID AND A.COM_ID = D.COM_ID AND A.CUST_ID = D.CUST_ID AND A.COM_ID = 'GZ0000000000004' AND A.BORN_DATE BETWEEN '20110227' AND '20110331' AND D.SALE_CENTER_ID ='GZ0000000000081' GROUP BY d.sale_center_id, c.item_id) sc on sc.item_id = b.item_id and sc.sale_center_id = b.sale_center_id WHERE B.COM_ID = A.COM_ID AND A.ITEM_ID = B.ITEM_ID AND B.ITEM_ID = SIU.ITEM_ID AND B.ITEM_ID = SIV.ITEM_ID AND SIU.UM_ID = '05' AND SIV.UM_ID = '03' AND B.COM_ID = 'GZ0000000000004' and b.date_begin = '20110227' and b.date_end = '20110331' and a.item_id in ('1330137') AND B.SALE_CENTER_ID='GZ0000000000081' GROUP BY f.organ_id,f.organ_name 表样: 列有很多三十多列 组一 1 2 3 4 5 组二 1 2 3 4 5 和 2 4 6 8 10
rollup也可以只对总表进行求和的,例如楼主的例子: -- 对所有组进行求和 GROUP BY rollup(f.organ_id,f.organ_name)-- 对总表进行求和(推荐使用) GROUP BY rollup((f.organ_id,f.organ_name))
-- 如果你不想使用rollup的话,就采用union方式附加个合计到结果集中。
with t as (
select id,sum(value) s from table1 group by id
)
select id,s from t
union
select '',sum(s) from t
sql如下:
SELECT f.organ_id,
f.organ_name organ_name,
COALESCE(SUM(B.PLAN_ORD * siu.um_size / siv.um_size), 0) ORD,
SUM(sc.HEJI)
FROM DIM_ITEM A,
SD_ITEM_UM_VIEW SIV,
SD_ITEM_UM_VIEW SIU,
SD_PLAN_DAY_GZ B
LEFT JOIN (SELECT organ_name, organ_id FROM pub_organ) F
ON b.sale_center_id = F.organ_id
full join (SELECT C.ITEM_ID, d.sale_center_id, sum(a.qty_sum) heji
FROM SD_CO A,
SD_CO_LINE B,
SD_ITEM C,
SD_ITEM_TOBACCO E,
RM_CUST D
WHERE A.CO_NUM = B.CO_NUM
AND B.ITEM_ID = C.ITEM_ID
AND B.ITEM_ID = E.ITEM_ID
AND A.COM_ID = D.COM_ID
AND A.CUST_ID = D.CUST_ID
AND A.COM_ID = 'GZ0000000000004'
AND A.BORN_DATE BETWEEN '20110227' AND '20110331'
AND D.SALE_CENTER_ID ='GZ0000000000081'
GROUP BY d.sale_center_id, c.item_id) sc
on sc.item_id = b.item_id
and sc.sale_center_id = b.sale_center_id
WHERE B.COM_ID = A.COM_ID
AND A.ITEM_ID = B.ITEM_ID
AND B.ITEM_ID = SIU.ITEM_ID
AND B.ITEM_ID = SIV.ITEM_ID
AND SIU.UM_ID = '05'
AND SIV.UM_ID = '03'
AND B.COM_ID = 'GZ0000000000004'
and b.date_begin = '20110227'
and b.date_end = '20110331'
and a.item_id in ('1330137')
AND B.SALE_CENTER_ID='GZ0000000000081'
GROUP BY f.organ_id,f.organ_name
表样:
列有很多三十多列
组一 1 2 3 4 5
组二 1 2 3 4 5
和 2 4 6 8 10
rollup也可以只对总表进行求和的,例如楼主的例子:
-- 对所有组进行求和
GROUP BY rollup(f.organ_id,f.organ_name)-- 对总表进行求和(推荐使用)
GROUP BY rollup((f.organ_id,f.organ_name))