表格如下: id1 id2 num sum
1 3 3 1000
2 3 4 1000
3 2 33 1000
4 1 12 1000
.. .. .. ...
.. .. .. 1000 注意:num有重复。希望得到如下类似的结果:id1 id2 num sum r
3 2 33 1000 3.3%
4 1 12 1000 4.5%
2 3 4 1000 4.9%
1 3 3 1000 5.2%分别按照不同的排序顺序计算累加的比例,id1或者id2 .
3 14 2
4 22 2
6 5 2
7 13 2
7 14 2
10 3 2
11 7 2
11 12 2
11 14 2
12 4 2
12 14 2
13 12 2
15 13 2
16 16 2
18 18 2
20 20 2
21 20 2
22 21 2
2 3 1
2 6 1
3 11 1
3 12 1
3 22 1
3 50 1
4 11 1
4 12 1
4 13 1
4 14 1
4 15 1
5 9 1
5 49 1
6 2 1
6 13 1
7 10 1
7 11 1
7 15 1
7 21 1
8 9 1
8 15 1
9 5 1
9 7 1
9 14 1
10 4 1
10 8 1
10 14 1
11 3 1
12 11 1
12 13 1
13 3 1
13 6 1
13 17 1
14 3 1
14 4 1
14 5 1
14 6 1
14 9 1
14 10 1
14 16 1
15 3 1
15 4 1
16 5 1
16 14 1
17 17 1
17 20 1
19 14 1
21 4 1
21 19 1
21 29 1
22 4 1
28 21 1
45 45 1
46 46 1
48 4 1
49 48 1
49 49 1
50 49 1
1 3 3 1000
2 3 4 1000
3 2 33 1000
4 1 12 1000
.. .. .. ...
.. .. .. 1000 注意:num有重复。希望得到如下类似的结果:id1 id2 num sum r
3 2 33 1000 3.3%
4 1 12 1000 4.5%
2 3 4 1000 4.9%
1 3 3 1000 5.2%分别按照不同的排序顺序计算累加的比例,id1或者id2 .
3 14 2
4 22 2
6 5 2
7 13 2
7 14 2
10 3 2
11 7 2
11 12 2
11 14 2
12 4 2
12 14 2
13 12 2
15 13 2
16 16 2
18 18 2
20 20 2
21 20 2
22 21 2
2 3 1
2 6 1
3 11 1
3 12 1
3 22 1
3 50 1
4 11 1
4 12 1
4 13 1
4 14 1
4 15 1
5 9 1
5 49 1
6 2 1
6 13 1
7 10 1
7 11 1
7 15 1
7 21 1
8 9 1
8 15 1
9 5 1
9 7 1
9 14 1
10 4 1
10 8 1
10 14 1
11 3 1
12 11 1
12 13 1
13 3 1
13 6 1
13 17 1
14 3 1
14 4 1
14 5 1
14 6 1
14 9 1
14 10 1
14 16 1
15 3 1
15 4 1
16 5 1
16 14 1
17 17 1
17 20 1
19 14 1
21 4 1
21 19 1
21 29 1
22 4 1
28 21 1
45 45 1
46 46 1
48 4 1
49 48 1
49 49 1
50 49 1
这次仅有两列,id1/id2
用我的数据,可以么?
insert into tbselect 3,14,2 union
select 4,22,2 union
select 6,5,2 union
select 7,13,2 union
select 7,14,2 union
select 10,3,2 union
select 11,7,2 union
select 11,12,2 union
select 11,14,2 union
select 12,4,2 union
select 12,14,2 union
select 13,12,2 union
select 15,13,2 union
select 16,16,2 union
select 18,18,2 union
select 20,20,2 union
select 21,20,2 union
select 22,21,2 union
select 2,3,1 union
select 2,6,1 union
select 3,11,1 union
select 3,12,1 union
select 3,22,1 union
select 3,50,1 union
select 4,11,1 union
select 4,12,1 union
select 4,13,1 union
select 4,14,1 union
select 4,15,1 union
select 5,9,1 union
select 5,49,1 union
select 6,2,1 union
select 6,13,1 union
select 7,10,1 union
select 7,11,1 union
select 7,15,1 union
select 7,21,1 union
select 8,9,1 union
select 8,15,1 union
select 9,5,1 union
select 9,7,1 union
select 9,14,1 union
select 10,4,1 union
select 10,8,1 union
select 10,14,1 union
select 11,3,1 union
select 12,11,1 union
select 12,13,1 union
select 13,3,1 union
select 13,6,1 union
select 13,17,1 union
select 14,3,1 union
select 14,4,1 union
select 14,5,1 union
select 14,6,1 union
select 14,9,1 union
select 14,10,1 union
select 14,16,1 union
select 15,3,1 union
select 15,4,1 union
select 16,5,1 union
select 16,14,1 union
select 17,17,1 union
select 17,20,1 union
select 19,14,1 union
select 21,4,1 union
select 21,19,1 union
select 21,29,1 union
select 22,4,1 union
select 28,21,1 union
select 45,45,1 union
select 46,46,1 union
select 48,4,1 union
select 49,48,1 union
select 49,49,1 union
select 50,49,1
比如id1 升序,id2 升序
或者id2 升序,id1升序把char(2)改成int
create table tb(id1 int, id2 int, num int)
2 3 1 1%
2 6 1 2%
.. 或者id1 id2 num ratio
6 2 1 1%
2 3 1 2%
... .. ...
insert into tb select 3,14,2 union
select 4,22,2 union
select 6,5,2 union
select 7,13,2 union
select 7,14,2 union
select 10,3,2 union
select 11,7,2 union
select 11,12,2 union
select 11,14,2 union
select 12,4,2 union
select 12,14,2 union
select 13,12,2 union
select 15,13,2 union
select 16,16,2 union
select 18,18,2 union
select 20,20,2 union
select 21,20,2 union
select 22,21,2 union
select 2,3,1 union
select 2,6,1 union
select 3,11,1 union
select 3,12,1 union
select 3,22,1 union
select 3,50,1 union
select 4,11,1 union
select 4,12,1 union
select 4,13,1 union
select 4,14,1 union
select 4,15,1 union
select 5,9,1 union
select 5,49,1 union
select 6,2,1 union
select 6,13,1 union
select 7,10,1 union
select 7,11,1 union
select 7,15,1 union
select 7,21,1 union
select 8,9,1 union
select 8,15,1 union
select 9,5,1 union
select 9,7,1 union
select 9,14,1 union
select 10,4,1 union
select 10,8,1 union
select 10,14,1 union
select 11,3,1 union
select 12,11,1 union
select 12,13,1 union
select 13,3,1 union
select 13,6,1 union
select 13,17,1 union
select 14,3,1 union
select 14,4,1 union
select 14,5,1 union
select 14,6,1 union
select 14,9,1 union
select 14,10,1 union
select 14,16,1 union
select 15,3,1 union
select 15,4,1 union
select 16,5,1 union
select 16,14,1 union
select 17,17,1 union
select 17,20,1 union
select 19,14,1 union
select 21,4,1 union
select 21,19,1 union
select 21,29,1 union
select 22,4,1 union
select 28,21,1 union
select 45,45,1 union
select 46,46,1 union
select 48,4,1 union
select 49,48,1 union
select 49,49,1 union
select 50,49,1 select id=identity(int,1,1),* into #
from tb
order by id1 asc,id2 descselect id1,id2
,cast((total*100.0/100) as varchar)+'%' ratio
from
(select *,total=(select sum(num) from # where id<=a.id)
from # a) adrop table tb,#/*
id1 id2 ratio
----------- ----------- -------------------------------
2 6 1.000000%
2 3 2.000000%
3 50 3.000000%
3 22 4.000000%
3 14 6.000000%
3 12 7.000000%
3 11 8.000000%
4 22 10.000000%
4 15 11.000000%
4 14 12.000000%
4 13 13.000000%
4 12 14.000000%
4 11 15.000000%
5 49 16.000000%
5 9 17.000000%
6 13 18.000000%
6 5 20.000000%
6 2 21.000000%
7 21 22.000000%
7 15 23.000000%
7 14 25.000000%
7 13 27.000000%
7 11 28.000000%
7 10 29.000000%
8 15 30.000000%
8 9 31.000000%
9 14 32.000000%
9 7 33.000000%
9 5 34.000000%
10 14 35.000000%
10 8 36.000000%
10 4 37.000000%
10 3 39.000000%
11 14 41.000000%
11 12 43.000000%
11 7 45.000000%
11 3 46.000000%
12 14 48.000000%
12 13 49.000000%
12 11 50.000000%
12 4 52.000000%
13 17 53.000000%
13 12 55.000000%
13 6 56.000000%
13 3 57.000000%
14 16 58.000000%
14 10 59.000000%
14 9 60.000000%
14 6 61.000000%
14 5 62.000000%
14 4 63.000000%
14 3 64.000000%
15 13 66.000000%
15 4 67.000000%
15 3 68.000000%
16 16 70.000000%
16 14 71.000000%
16 5 72.000000%
17 20 73.000000%
17 17 74.000000%
18 18 76.000000%
19 14 77.000000%
20 20 79.000000%
21 29 80.000000%
21 20 82.000000%
21 19 83.000000%
21 4 84.000000%
22 21 86.000000%
22 4 87.000000%
28 21 88.000000%
45 45 89.000000%
46 46 90.000000%
48 4 91.000000%
49 49 92.000000%
49 48 93.000000%
50 49 94.000000%(所影响的行数为 76 行)
*/
insert into tb select 3,14,2 union
select 4,22,2 union
select 6,5,2 union
select 7,13,2 union
select 7,14,2 union
select 10,3,2 union
select 11,7,2 union
select 11,12,2 union
select 11,14,2 union
select 12,4,2 union
select 12,14,2 union
select 13,12,2 union
select 15,13,2 union
select 16,16,2 union
select 18,18,2 union
select 20,20,2 union
select 21,20,2 union
select 22,21,2 union
select 2,3,1 union
select 2,6,1 union
select 3,11,1 union
select 3,12,1 union
select 3,22,1 union
select 3,50,1 union
select 4,11,1 union
select 4,12,1 union
select 4,13,1 union
select 4,14,1 union
select 4,15,1 union
select 5,9,1 union
select 5,49,1 union
select 6,2,1 union
select 6,13,1 union
select 7,10,1 union
select 7,11,1 union
select 7,15,1 union
select 7,21,1 union
select 8,9,1 union
select 8,15,1 union
select 9,5,1 union
select 9,7,1 union
select 9,14,1 union
select 10,4,1 union
select 10,8,1 union
select 10,14,1 union
select 11,3,1 union
select 12,11,1 union
select 12,13,1 union
select 13,3,1 union
select 13,6,1 union
select 13,17,1 union
select 14,3,1 union
select 14,4,1 union
select 14,5,1 union
select 14,6,1 union
select 14,9,1 union
select 14,10,1 union
select 14,16,1 union
select 15,3,1 union
select 15,4,1 union
select 16,5,1 union
select 16,14,1 union
select 17,17,1 union
select 17,20,1 union
select 19,14,1 union
select 21,4,1 union
select 21,19,1 union
select 21,29,1 union
select 22,4,1 union
select 28,21,1 union
select 45,45,1 union
select 46,46,1 union
select 48,4,1 union
select 49,48,1 union
select 49,49,1 union
select 50,49,1
select px = identity(int,1,1),* into # from tb order by id1 ,id2select b.id1,b.id2,b.num,ratio =ltrim(num1*100/a) + '%'
from (select id1,id2,num,num1 = ( select sum(num) from # where px <= d.px) from # d ) b
,(select sum(num) as a from tb) cdrop table tb,#