使用ROLLUP 运算符:试一试 SELECT CASE WHEN (GROUPING(地区) = 1) THEN '合计' ELSE ISNULL(地区, 'UNKNOWN') END AS 地区, CASE WHEN (GROUPING(客户) = 1) THEN '合计' ELSE ISNULL(客户, 'UNKNOWN') END AS 客户, CASE WHEN (GROUPING(物料分类) = 1) THEN '合计' ELSE ISNULL(物料分类, 'UNKNOWN') END AS 客户, SUM(目标数量) AS 目标数量 FROM tablename GROUP BY 地区, 客户,目标数量 WITH ROLLUP希望对你有帮助
也可以使用Cube运算符 使用ROLLUP 运算符:试一试 SELECT CASE WHEN (GROUPING(地区) = 1) THEN '合计' ELSE ISNULL(地区, 'UNKNOWN') END AS 地区, CASE WHEN (GROUPING(客户) = 1) THEN '合计' ELSE ISNULL(客户, 'UNKNOWN') END AS 客户, CASE WHEN (GROUPING(物料分类) = 1) THEN '合计' ELSE ISNULL(物料分类, 'UNKNOWN') END AS 客户, SUM(目标数量) AS 目标数量 FROM tablename GROUP BY 地区, 客户,目标数量 WITH Cube希望对你有帮助
可以把所要的结果送到变量再输出硬要这样的输出结果,可以select * into #bj from material where area='北京' select * from #bj order by customer compute sum(qty) by customer select material,sum(qty) from #bj group by material select '北京地区合计 = ',sum(qty) from #bj select * into #sh from material where area='上海' select * from #sh order by customer compute sum(qty) by customer select material,sum(qty) from #sh group by material select '上海地区合计 = ',sum(qty) from #sh GO如果地区不定,可用游标读出,在循环内用exec执行
select 'a' as 统计顺序1,'a' as 统计顺序2,地区,客户,物料分类,目标数量 from table union select 'a' as 统计类别1,'b' as 统计类别2,地区,客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a union select 'b' as 统计类别1,'b' as 统计类别2,地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a union select 'b' as 统计类别1,'c' as 统计类别2,地区,'' as 客户,'' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a order by 地区,统计类别1,客户,统计顺序2
更正 select 'a' as 统计顺序1,'a' as 统计顺序2,地区,客户,物料分类,目标数量 from table union select 'a' as 统计顺序1,'b' as 统计顺序2,地区,客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a union select 'b' as 统计顺序1,'b' as 统计顺序2,地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a union select 'b' as 统计顺序1,'c' as 统计顺序2,地区,'' as 客户,'' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a order by 地区,统计顺序1,客户,统计顺序2
再更正 select 'a' as 统计顺序1,'a' as 统计顺序2,地区,客户,物料分类,目标数量 from table union select 'a' as 统计顺序1,'b' as 统计顺序2,地区,TRIM(客户)+'合计' as 客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a union select 'b' as 统计顺序1,'b' as 统计顺序2,TRIM(地区)+'合计' as 地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a union select 'b' as 统计顺序1,'c' as 统计顺序2,TRIM(地区)+'合计' as 地区,'' as 客户,'合计' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a order by 地区,统计顺序1,客户,统计顺序2,物料分类
union
select 地区,分类='合计',sum(数量) from tbl group by 地区北京合计=300?
还是你要用你的程序放映到页面上给别人看?
如果不是直接让别人在你的查询分析器里看结果,那么不用这么费事的一定要查出这样结构的结果,可以变通一下,分组查出你要的不同地区,不同物料的统计值后,用程序去控制你在页面上的表现形式,我觉得这样会好一些。
SELECT CASE WHEN (GROUPING(地区) = 1) THEN '合计'
ELSE ISNULL(地区, 'UNKNOWN')
END AS 地区,
CASE WHEN (GROUPING(客户) = 1) THEN '合计'
ELSE ISNULL(客户, 'UNKNOWN')
END AS 客户,
CASE WHEN (GROUPING(物料分类) = 1) THEN '合计'
ELSE ISNULL(物料分类, 'UNKNOWN')
END AS 客户,
SUM(目标数量) AS 目标数量
FROM tablename
GROUP BY 地区, 客户,目标数量 WITH ROLLUP希望对你有帮助
使用ROLLUP 运算符:试一试
SELECT CASE WHEN (GROUPING(地区) = 1) THEN '合计'
ELSE ISNULL(地区, 'UNKNOWN')
END AS 地区,
CASE WHEN (GROUPING(客户) = 1) THEN '合计'
ELSE ISNULL(客户, 'UNKNOWN')
END AS 客户,
CASE WHEN (GROUPING(物料分类) = 1) THEN '合计'
ELSE ISNULL(物料分类, 'UNKNOWN')
END AS 客户,
SUM(目标数量) AS 目标数量
FROM tablename
GROUP BY 地区, 客户,目标数量 WITH Cube希望对你有帮助
select * from #bj order by customer compute sum(qty) by customer
select material,sum(qty) from #bj group by material
select '北京地区合计 = ',sum(qty) from #bj
select * into #sh from material where area='上海'
select * from #sh order by customer compute sum(qty) by customer
select material,sum(qty) from #sh group by material
select '上海地区合计 = ',sum(qty) from #sh
GO如果地区不定,可用游标读出,在循环内用exec执行
union
select 'a' as 统计类别1,'b' as 统计类别2,地区,客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a
union
select 'b' as 统计类别1,'b' as 统计类别2,地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a
union
select 'b' as 统计类别1,'c' as 统计类别2,地区,'' as 客户,'' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a
order by 地区,统计类别1,客户,统计顺序2
select 'a' as 统计顺序1,'a' as 统计顺序2,地区,客户,物料分类,目标数量 from table
union
select 'a' as 统计顺序1,'b' as 统计顺序2,地区,客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a
union
select 'b' as 统计顺序1,'b' as 统计顺序2,地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a
union
select 'b' as 统计顺序1,'c' as 统计顺序2,地区,'' as 客户,'' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a
order by 地区,统计顺序1,客户,统计顺序2
select 'a' as 统计顺序1,'a' as 统计顺序2,地区,客户,物料分类,目标数量 from table
union
select 'a' as 统计顺序1,'b' as 统计顺序2,地区,TRIM(客户)+'合计' as 客户,'' as 物料分类,目标数量 from (select 地区,客户,sum(目标数量) as 目标数量 from table group by 地区,客户) as a
union
select 'b' as 统计顺序1,'b' as 统计顺序2,TRIM(地区)+'合计' as 地区,'' as 客户,物料分类,目标数量 from (select 地区,物料分类,sum(目标数量) as 目标数量 from table group by 地区,物料分类) as a
union
select 'b' as 统计顺序1,'c' as 统计顺序2,TRIM(地区)+'合计' as 地区,'' as 客户,'合计' as 物料分类,目标数量 from (select 地区,sum(目标数量) as 目标数量 from table group by 地区) as a
order by 地区,统计顺序1,客户,统计顺序2,物料分类