再问统计多列百分比
查询得到如下结果:
f g r 数量
-1-1-125
-1-1015
-1-1110
-10-118
-1003
-10113
-11-124
-1106
-1117
0-1-134
0-1017
0-1118
00-123
00010
00113
01-118
01013
01113
1-1-118
1-1021
1-119
10-116
1009
10111
11-115
1104
11118
F/G/R分别有三种组合-1,0,1 数量就是普通的整数> 0
希望得到下面的结果:
-1-1-150%
-1-1030%
-1-1120%
-10-153%
-1009%
-10138%
-11-165%
-11016%
-11119%
... ... ... ...
把27种F/G/R排列分别算出来.
F/G不变,R变化,F/G/R三种排列所占的百分比
[解决办法]
create table tab(f varchar(2),g varchar(2),r varchar(2),数量 int)
insert tab
select '-1 ', '-1 ', '-1 ',25
union all
select '-1 ', '-1 ', '0 ',15
union all
select '-1 ', '-1 ', '1 ',10
union all
select '-1 ', '0 ', '-1 ',18
union all
select '-1 ', '0 ', '0 ',3
union all
select '-1 ', '0 ', '1 ',13
union all
select '-1 ', '1 ', '-1 ',24
union all
select '-1 ', '1 ', '0 ',6
union all
select '-1 ', '1 ', '1 ',7
union all
select '0 ', '-1 ', '-1 ',34
union all
select '0 ', '-1 ', '0 ',17
union all
select '0 ', '-1 ', '1 ',18
union all
select '0 ', '0 ', '-1 ',23
union all
select '0 ', '0 ', '0 ',10
union all
select '0 ', '0 ', '1 ',13
union all
select '0 ', '1 ', '-1 ',18
union all
select '0 ', '1 ', '0 ',13
union all
select '0 ', '1 ', '1 ',13
union all
select '1 ', '-1 ', '-1 ',18
union all
select '1 ', '-1 ', '0 ',21
union all
select '1 ', '-1 ', '1 ',9
union all
select '1 ', '0 ', '-1 ',16
union all
select '1 ', '0 ', '0 ',9
union all
select '1 ', '0 ', '1 ',11
union all
select '1 ', '1 ', '-1 ',15
union all
select '1 ', '1 ', '0 ',4
union all
select '1 ', '1 ', '1 ',18
select f,g,r,cast(sum(数量)*100/(select sum(数量) from tab t1 where t1.f=t2.f and t1.g=t2.g) AS varchar(10))+ '% ' from tab t2
group by f,g,r
order by f desc,g desc,r desc
drop table tab
