如果上面就是你的要求,那么在SQL SERVER中可以如此:
SELECT CASE WHEN (GROUPING(产品类型) = 1) THEN '合计'
ELSE ISNULL(产品类型, 'UNKNOWN')
END AS 产品类型,
CASE WHEN (GROUPING(产品类型) = 1 and GROUPING(产品名称) = 1) THEN '合计'
WHEN (GROUPING(产品类型) = 0 and GROUPING(产品名称) = 1) THEN '小计'
ELSE ISNULL(产品名称, 'UNKNOWN')
END AS 产品名称,
SUM(数量) AS 数量
FROM YourTable
GROUP BY 产品类型,产品名称 WITH ROLLUP
SELECT CASE WHEN TYPE=2 THEN '小计' ELSE 名称 END AS 名称,CASE WHEN TYPE=2 THEN ' ' ELSE 国标代号 END AS 国标代号,数量,项号,安装代号...
FROM (
SELECT 名称,国标代号,数量,1 AS TYPE,项号,安装代号...
FROM NAME
UNIT ALL
SELECT 名称,国标代号,SUM(数量) AS 数量,2 AS TYPE,'' AS 项号,'' AS 安装代号...
FROM NAME
GROUP BY 名称,国标代号
)
ORDER BY 名称,国标代号,TYPE
select class,total,field1,field2…… from (select class," " as total,field1,field2,field3…… from tablename
union
select class,"小计" as total,sum(field1) as field1,sum(field2) as field2,…… from tablename) c order by class,total
字段:类别,数量,其它字段
表名:aa
1、select 类别,数量,其它字段 into #temp1 from aa order by 类别
2、select identity(int,1,1) id,* into #temp2 from #temp1
3、select * from
(select max(id)+1 id,类别,sum(数量),其它字段填上与字段相同类别的常数 from #temp2 group by 类别 union all select * from #temp2) bb
order by 类别,id
输出结果就是你所要的。再删除#temp1,#temp2
SELECT CASE WHEN TYPE=2 THEN '小计' ELSE CLASS END AS CLASS,QTY
FROM (
SELECT class,qty,1 AS TYPE FROM TABLENAME
UNIT ALL
SELECT CLASS,SUN(QTY) AS QTY,2 AS TYPE FROM TABLENAME
GROUP BY CLASS
) AS A
ORDER BY CLASS,TYPE