用SQL查询一批数据，怎么在最后一行增加个数字合计

[lq634569163]

在下面添加一个合计行 把每一列的数据在下面做出汇总
这是sql 语句
SELECT CASE ftypeid WHEN 20013 THEN '板材' WHEN 20014 THEN '线条' WHEN 40215 THEN '五金'

WHEN 40216 THEN '木方'WHEN 40217 THEN '工艺门'WHEN 40218 THEN '工艺品'WHEN 40219 THEN '油漆' END AS fname ,

round(SUM(D),2) as'赊销金额',
round(SUM(E),2) as'赊销折扣',
round(SUM(F),2) as'赊销净额',
round(SUM(G),2) as'现销金额',
round(SUM(H),2) as'现销折扣',
round(SUM(J),2) as'现销净额',
round(SUM(D+G),2) as'总金额',
round(SUM(E+H),2) as'总折扣',
round(SUM(F+J),2) as'总净额'
FROM ( SELECT b.fname ,
CASE WHEN c.fsalestyle = 101 THEN round((d.fauxqty*d.fconsignprice),2)
ELSE 0
END D ,
CASE WHEN c.fsalestyle = 101 THEN d.FDiscountAmount
ELSE 0
END E ,
CASE WHEN c.fsalestyle = 101 THEN d.FConsignAmount
ELSE 0
END F ,
CASE WHEN c.fsalestyle = 100 THEN round((d.fauxqty*d.fconsignprice),2)
ELSE 0
END G ,
CASE WHEN c.fsalestyle = 100 THEN d.FDiscountAmount
ELSE 0
END H ,
CASE WHEN c.fsalestyle = 100 THEN d.FConsignAmount
ELSE 0
END J ,

a.FTypeID
FROM [AIS20130930153452].[dbo].[t_ICItemBase] a
INNER JOIN [AIS20130930153452].[dbo].[t_item] b ON a.FItemID = b.FItemID
LEFT JOIN [AIS20130930153452].[dbo].[ICStockBillEntry] d ON d.fitemid=b.fitemid
left join [AIS20130930153452].[dbo].[icstockbill] c on c.finterid=d.FInterID
left join [AIS20130930153452].[dbo].[t_stock] e on e.fitemid=d.fdcstockid
WHERE b.FItemClassID = 4
AND c.FTranType = 21



) G
GROUP BY CASE ftypeid WHEN 20013 THEN '板材' WHEN 20014 THEN '线条' WHEN 40215 THEN '五金'
WHEN 40216 THEN '木方'WHEN 40217 THEN '工艺门'WHEN 40218 THEN '工艺品'WHEN 40219 THEN '油漆' END

[-Tracy-McGrady-]

在from里面写小结，外面在写select

[Andy__Huang]

后面加with rollup

select aaa,bbb,sum(a1) as m,sum(a2) as p,sum(a3) as s 
from tb
where (条件表达式)
group by aaa,bbb
with rollup

[ai_li7758521]

;
WITH    cte
          AS ( SELECT   CASE ftypeid
                          WHEN 20013 THEN '板材'
                          WHEN 20014 THEN '线条'
                          WHEN 40215 THEN '五金'
                          WHEN 40216 THEN '木方'
                          WHEN 40217 THEN '工艺门'
                          WHEN 40218 THEN '工艺品'
                          WHEN 40219 THEN '油漆'
                        END AS fname ,
                        ROUND(SUM(D), 2) AS '赊销金额' ,
                        ROUND(SUM(E), 2) AS '赊销折扣' ,
                        ROUND(SUM(F), 2) AS '赊销净额' ,
                        ROUND(SUM(G), 2) AS '现销金额' ,
                        ROUND(SUM(H), 2) AS '现销折扣' ,
                        ROUND(SUM(J), 2) AS '现销净额' ,
                        ROUND(SUM(D + G), 2) AS '总金额' ,
                        ROUND(SUM(E + H), 2) AS '总折扣' ,
                        ROUND(SUM(F + J), 2) AS '总净额'
               FROM     ( SELECT    b.fname ,
                                    CASE WHEN c.fsalestyle = 101
                                         THEN ROUND(( d.fauxqty
                                                      * d.fconsignprice ), 2)
                                         ELSE 0
                                    END D ,
                                    CASE WHEN c.fsalestyle = 101
                                         THEN d.FDiscountAmount
                                         ELSE 0
                                    END E ,
                                    CASE WHEN c.fsalestyle = 101
                                         THEN d.FConsignAmount
                                         ELSE 0
                                    END F ,
                                    CASE WHEN c.fsalestyle = 100
                                         THEN ROUND(( d.fauxqty
                                                      * d.fconsignprice ), 2)
                                         ELSE 0
                                    END G ,
                                    CASE WHEN c.fsalestyle = 100
                                         THEN d.FDiscountAmount
                                         ELSE 0
                                    END H ,
                                    CASE WHEN c.fsalestyle = 100
                                         THEN d.FConsignAmount
                                         ELSE 0
                                    END J ,
                                    a.FTypeID
                          FROM      [AIS20130930153452].[dbo].[t_ICItemBase] a
                                    INNER JOIN [AIS20130930153452].[dbo].[t_item] b ON a.FItemID = b.FItemID
                                    LEFT JOIN [AIS20130930153452].[dbo].[ICStockBillEntry] d ON d.fitemid = b.fitemid
                                    LEFT JOIN [AIS20130930153452].[dbo].[icstockbill] c ON c.finterid = d.FInterID
                                    LEFT JOIN [AIS20130930153452].[dbo].[t_stock] e ON e.fitemid = d.fdcstockid
                          WHERE     b.FItemClassID = 4
                                    AND c.FTranType = 21
                        ) G
               GROUP BY CASE ftypeid
                          WHEN 20013 THEN '板材'
                          WHEN 20014 THEN '线条'
                          WHEN 40215 THEN '五金'
                          WHEN 40216 THEN '木方'
                          WHEN 40217 THEN '工艺门'
                          WHEN 40218 THEN '工艺品'
                          WHEN 40219 THEN '油漆'
                        END
             )
    SELECT  *
    FROM    cte
    UNION ALL
    SELECT  '合计' ,
            SUM(赊销金额) ,
            SUM(赊销折扣) ,
            SUM(赊销净额) ,
            SUM(现销金额) ,
            SUM(现销折扣) ,
            SUM(现销净额) ,
            SUM(总金额) ,
            SUM(总折扣) ,
            SUM(总净额)
    FROM    cte

[lq634569163]

with rollup 加了之后可以 但是第一列的Null 怎么修改

[LongRui888]

引用 4 楼 lq634569163 的回复:

with rollup 加了之后可以 但是第一列的Null 怎么修改

你用的2000，还是2005,2008的sql server？

[LongRui888]

最上面的：

CASE ftypeid 
WHEN 20013 THEN '板材' 
WHEN 20014 THEN '线条' 
WHEN 40215 THEN '五金'
WHEN 40216 THEN '木方'
WHEN 40217 THEN '工艺门'
WHEN 40218 THEN '工艺品'
WHEN 40219 THEN '油漆' 
else '汇总'
END AS fname

[LongRui888]

也可以用grouping，返回1表示，null是rollup产生的汇总项： case when grouping( column_name ) = 1 then '汇总' else '其他列' end

[t101lian]

引用 2 楼 hdhai9451 的回复:

后面加with rollup

select aaa,bbb,sum(a1) as m,sum(a2) as p,sum(a3) as s 
from tb
where (条件表达式)
group by aaa,bbb
with rollup

为空很好解决呀，， 借用这位大牛的语句

select case when isnull(aaa,'')='' then '合计' end as fname,bbb,sum(a1) as m,
sum(a2) as p,sum(a3) as s 
from tb
where (条件表达式)
group by aaa,bbb
with rollup