只能自己写三段去Unionselect * from
(SELECT
BRAND_NAME AS 品牌,
PRODUCT_NAME AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
GROUP BY BRAND_NAME, PRODUCT_NAME
union all
SELECT
BRAND_NAME AS 品牌,
'小计' AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
GROUP BY BRAND_NAME
union all
SELECT
'合计' AS 品牌,
'合计' AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
)
ORDER BY 品牌, 卷烟名称
(SELECT
BRAND_NAME AS 品牌,
PRODUCT_NAME AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
GROUP BY BRAND_NAME, PRODUCT_NAME
union all
SELECT
BRAND_NAME AS 品牌,
'小计' AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
GROUP BY BRAND_NAME
union all
SELECT
'合计' AS 品牌,
'合计' AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
)
ORDER BY 品牌, 卷烟名称
case when BRAND_NAME is null then '汇总' else BRAND_NAME end AS 品牌,
case when PRODUCT_NAME is null and BRAND_NAME is null then '汇总'
when PRODUCT_NAME is null then '小计'
else PRODUCT_NAME end AS 卷烟名称,
SUM(a.SALES_NUM_Z / 10000) AS [销售数量(万支)],
SUM(a.SALES_AMT / 10000) AS [销售金额(万元)]
FROM SL_SL_COMPANYPDTMONTH a
INNER JOIN CD_PT_PRODUCT b ON a.PRODUCT_ID = b.PRODUCT_ID
INNER JOIN CD_PT_BRAND c ON b.BRAND_ID = c.BRAND_ID
WHERE MONTH_ID = 200809
GROUP BY BRAND_NAME, PRODUCT_NAME with rollup
ORDER BY 品牌, 卷烟名称