1 CREATE TABLE #t1(
2 PlantNo
varchar(
4),
3 vendorCode
varchar(
10),
4 amount
decimal(
18,
2))
5
6 insert #t1
values(
'P112',
'1',
10)
7 insert #t1
values(
'P112',
'2',
20)
8 insert #t1
values(
'P112',
'3',
30)
9 insert #t1
values(
'P112',
'4',
40)
10 insert #t1
values(
'P115',
'1',
10)
11
12 select #t1.plantno,vendorCode,amount,amount
as amountTotal,amount
as amountTotal2,amount
as rate
13 into #t2
14 from #t1
order by plantNo,amount
desc
15
16 update #t2
set amountTotal
=(
select SUM(amount)
from #t1
where #t1.PlantNo
=#t2.PlantNo)
17 update #t2
set amountTotal2
=(
select SUM(amount)
from #t1
where #t1.PlantNo
=#t2.PlantNo
and amount
>=#t2.amount)
18 update #t2
set rate
=amountTotal2
/amountTotal
19 select * from #t2
20 drop table #t1
21 drop table #t2
22
23 --結果
24 plantno vendorCode amount amountTotal amountTotal2 rate
25 P112
4 40.00 100.00 40.00 0.40
26 P112
3 30.00 100.00 70.00 0.70
27 P112
2 20.00 100.00 90.00 0.90
28 P112
1 10.00 100.00 100.00 1.00
29 P115
1 10.00 10.00 10.00 1.00
這部分程序的作用是統計前幾大累計的佔比問題。 比如:最富的那個人占了2%,第二的人占了1.5%，那麼前2個人累計占比3.5%。
转载于:https://www.cnblogs.com/wonder223/p/5870991.html
