declare @t table(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert into @t(等级,城市,分店名,数量)
select '一级', '广州', 'A1', 100 union all
select '二级', '广州', 'A2', 150 union all
select '二级', '广州', 'A3', 150 union all
select '一级', '深圳', 'B1', 200 union all
select '三级', '深圳', 'B2', 200 union all
select '三级', '深圳', 'B3', 200 union all
select '三级', '珠海', 'C1', 150 union all
select '二级', '珠海', 'C2', 150 union all
select '一级', '珠海', 'C3', 150select tem.等级,tem.城市,sum(isnull(t.数量,0)) 数量
from @t t right join
(select a.等级,b.城市 from (select distinct 等级 from @t) a,(select distinct 城市 from @t) b) tem
on t.等级=tem.等级 and t.城市=tem.城市
group by tem.等级,tem.城市 order by tem.等级,tem.城市至于百分比、合计等,由应用程序去组织
insert into @t(等级,城市,分店名,数量)
select '一级', '广州', 'A1', 100 union all
select '二级', '广州', 'A2', 150 union all
select '二级', '广州', 'A3', 150 union all
select '一级', '深圳', 'B1', 200 union all
select '三级', '深圳', 'B2', 200 union all
select '三级', '深圳', 'B3', 200 union all
select '三级', '珠海', 'C1', 150 union all
select '二级', '珠海', 'C2', 150 union all
select '一级', '珠海', 'C3', 150select tem.等级,tem.城市,sum(isnull(t.数量,0)) 数量
from @t t right join
(select a.等级,b.城市 from (select distinct 等级 from @t) a,(select distinct 城市 from @t) b) tem
on t.等级=tem.等级 and t.城市=tem.城市
group by tem.等级,tem.城市 order by tem.等级,tem.城市至于百分比、合计等,由应用程序去组织
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
go--统计
select 等级=case
when grouping(a.城市)=0 and max(c.城市) is not null
then a.等级 else '' end
,城市=case
when grouping(a.城市)=1
then '合计' else a.城市 end
,数量=sum(b.数量)
,百分比=cast(cast(
case
when grouping(a.城市)=1
then case
when(select sum(数量) from tb)=0
then 0.0
else sum(b.数量)*100.0
/(select sum(数量) from tb) end
else case
when max(d.数量)=0
then 0.9
else sum(b.数量)*100.0
/max(d.数量) end
end as decimal(10,2)) as varchar)+'%'
from(
select *
from(select distinct 等级 from tb)a
,(select distinct 城市 from tb)b
)a
left join tb b on a.等级=b.等级 and a.城市=b.城市
left join(
select 城市=min(城市) from tb
)c on a.城市=c.城市
left join(
select 等级,数量=sum(数量) from tb group by 等级
)d on a.等级=d.等级
group by a.等级,a.城市 with rollup
having grouping(a.等级)=0
order by charindex(a.等级,'一级二级三级')
,grouping(a.城市)
,a.城市go--删除测试
drop table tb/*--测试结果等级 城市 数量 百分比
---------- ---------- ----------- --------------
一级 广州 100 22.22%
深圳 200 44.44%
珠海 150 33.33%
合计 450 31.03%
二级 广州 300 66.67%
深圳 NULL NULL
珠海 150 33.33%
合计 450 31.03%
三级 广州 NULL NULL
深圳 400 72.73%
珠海 150 27.27%
合计 550 37.93%(所影响的行数为 12 行)--*/