try:select 类别,厂家,品牌,商品,期末,销售,损耗
from
(
select 类别,厂家,品牌,商品,
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 1 as flag
from 表 group by 类别,厂家,品牌,商品
Union all
select 类别,厂家,品牌,'',
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 2 as flag
from 表 group by 类别,厂家,品牌
Union all
select 类别,厂家,'','',
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 3 as flag
from 表 group by 类别,厂家
Union all
select 类别,'','',''
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 4 as flag
from 表 group by 类别
Union all
select '','','',''
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 5 as flag
from 表
) aa
order by flag,类别,厂家,品牌,商品
from
(
select 类别,厂家,品牌,商品,
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 1 as flag
from 表 group by 类别,厂家,品牌,商品
Union all
select 类别,厂家,品牌,'',
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 2 as flag
from 表 group by 类别,厂家,品牌
Union all
select 类别,厂家,'','',
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 3 as flag
from 表 group by 类别,厂家
Union all
select 类别,'','',''
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 4 as flag
from 表 group by 类别
Union all
select '','','',''
sum(期初) as 期初,sum(期末) as 期末,sum(销售) 销售 ,sum(损耗) 损耗, 5 as flag
from 表
) aa
order by flag,类别,厂家,品牌,商品
解决方案 »
- 存储过程中字段更新的问题
- 添加smalldatetime类型数据的问题
- 如何对sql server 2005建立的数据库加密???
- 如何从一次动态执行SQL语句中返回变量值
- 日期的比较问题
- 根据A表更新B表数据,update数据操作写法问题
- 请问怎样用sql语句重新启动sql server,得在存储过程里好使才可以?有人会吗?
- 请教关于合计行的问题
- 急:在公司建的一个数据库,现在想把公司的数据库(所有表和数据)转移到家里的机器
- sql server行列转置 [
- win2003 server 下装 sqlserver 2000 + sp3,为什么其他机器访问不了?(50分)
- 这是叫做分发数据库吗?请各位大虾指点迷律!
(
[类别] char(64) not null,
[厂家] char(64) not null,
[品牌] char(64) not null,
[商品] char(64) not null,
[期初] numeric(19,6) not null,
[期末] numeric(19,6) not null,
[销售] numeric(19,6) not null,
[损耗] numeric(19,6) not null
)
goinsert into #t values('类别A', '厂家1', '品牌a', '商品1', 100, 90, 8, 2)
insert into #t values('类别A', '厂家1', '品牌a', '商品2', 100, 95, 5, 0)
insert into #t values('类别A', '厂家1', '品牌b', '商品3', 100, 90, 8, 2)
insert into #t values('类别A', '厂家1', '品牌b', '商品4', 100, 95, 5, 0)
go--推荐一种方法
select
case
when (grouping([类别])=1) then '总计'
else
case
when (grouping([厂家])=1) then isnull(rtrim([类别]), '')
else
case
when (grouping([品牌])=1) then space(4)+isnull(rtrim([厂家]), '')
else
case
when (grouping([商品])=1) then space(4*2)+isnull(rtrim([品牌]), '')
else space(4*3)+isnull(rtrim([商品]), '')
end
end
end
end as xm, sum([期初]) as [期初],
sum([期末]) as [期末],
sum([销售]) as [销售],
sum([损耗]) as [损耗]
from #t
group by [类别], [厂家], [品牌], [商品] with rollup
order by
grouping([类别]) desc, [类别],
grouping([厂家]) desc, [厂家],
grouping([品牌]) desc, [品牌],
grouping([商品]) desc, [商品]--结果
期初 期末 销售 损耗
总计 400 370 26 4
类别A 400 370 26 4
厂家1 400 370 26 4
品牌a 200 185 13 2
商品1 100 90 8 2
商品2 100 95 5 0
品牌b 200 185 13 2
商品3 100 90 8 2
商品4 100 95 5 0