关于SQL分组统计语句,该如何解决_Sql Server

关于SQL分组统计语句
请教如果用sql语句查询下表A得出B表

------解决方案--------------------

SQL code

;with cte as(select 姓名,类型,sum(衣服*金额)衣服,sum(裤子*金额)裤子,sum(鞋子*金额)鞋子,sum((isnull(衣服,0)+isnull(裤子,0)+isnull(鞋子,0))*金额)总金额from tb group by 姓名,类型)select * from cteunion allselect 类型+'总计金额',类型,sum(衣服),sum(裤子),sum(鞋子),sum(总金额) from cte group by 类型 order by 类型,姓名
------解决方案--------------------
SQL code--刚才只替换了一部分select * from (select 姓名,类型,sum(衣服*金额)衣服,sum(裤子*金额)裤子,sum(鞋子*金额)鞋子,sum((isnull(衣服,0)+isnull(裤子,0)+isnull(鞋子,0))*金额)总金额from tb group by 姓名,类型) aaunion allselect 类型+'总计金额',类型,sum(衣服),sum(裤子),sum(鞋子),sum(总金额) from （select 姓名,类型,sum(衣服*金额)衣服,sum(裤子*金额)裤子,sum(鞋子*金额)鞋子,sum((isnull(衣服,0)+isnull(裤子,0)+isnull(鞋子,0))*金额)总金额from tb group by 姓名,类型）bbgroup by 类型 order by 类型,姓名