求sql：得到所占份额及累计份额

福尔摩小妖 2010-10-11 10:45:32

求sql：得到所占份额及累计份额



create table t_sum_abc(num int,material varchar(20),amount numeric(9,2))



insert t_sum_abc

select ROW_NUMBER() over(order by amount desc) num ,material,amount

from(

	select 'DM0000000001' material,100 amount

	union all

	select 'DM0000000002',25

	union all

	select 'DM0000000003',80

	union all

	select 'DM0000000004',200

	union all

	select 'DM0000000005',250

	union all

	select 'DM0000000006',50

	union all

	select 'DM0000000007',75

	union all

	select 'DM0000000008',60

	union all

	select 'DM0000000009',80

) _a

希望得到结果：
num material amount 金额所占份额金额累计份额
1 DM0000000005 250.00 27.17% 27.17%
2 DM0000000004 200.00 21.74% 48.91%
3 DM0000000001 100.00 10.87% 59.78%
4 DM0000000009 80.00 8.70% 68.48%
5 DM0000000003 80.00 8.70% 77.18%
6 DM0000000007 75.00 8.15% 85.33%
7 DM0000000008 60.00 6.52% 91.85%
8 DM0000000006 50.00 5.43% 97.28%
9 DM0000000002 25.00 2.72% 100.00%

...全文

111 点赞收藏 5

写回复

5 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

coleling 2010-10-12

我习惯用联接解决问题：（似乎效率要高一点点）



select a.num, a.material, a.amount, cast(cast(a.amount/c.totalamount*100 as numeric(10,2)) as varchar(10))+'%' as 金额所占份额, cast(cast(b.sumamount/c.totalamount*100 as numeric(10,2)) as varchar(10))+'%'  AS 金额累计份额

from t_sum_abc a

join 

(

	select a.num, sum(b.amount) as sumamount

	from t_sum_abc a

	join t_sum_abc b on a.num >= b.num

	group by a.num

) b on a.num = b.num

cross join 

(

	select sum(amount) as totalamount from t_sum_abc

) c

回复赞

SQL2088 2010-10-11

select 

	num ,

	material,

	amount,

	金额所占份额=ltrim(cast(amount*100.0/(select sum(amount) from t_sum_abc)as decimal(18,2)))+'%',

	金额累计份额=ltrim(cast((select sum(amount) from t_sum_abc where num<=t.num)*100.0/(select sum(amount) from t_sum_abc)as decimal(18,2)))+'%'

from t_sum_abc t



num         material             amount                                  金额所占份额                                     金额累计份额

----------- -------------------- --------------------------------------- ------------------------------------------ ------------------------------------------

1           DM0000000005         250.00                                  27.17%                                     27.17%

2           DM0000000004         200.00                                  21.74%                                     48.91%

3           DM0000000001         100.00                                  10.87%                                     59.78%

4           DM0000000009         80.00                                   8.70%                                      68.48%

5           DM0000000003         80.00                                   8.70%                                      77.17%

6           DM0000000007         75.00                                   8.15%                                      85.33%

7           DM0000000008         60.00                                   6.52%                                      91.85%

8           DM0000000006         50.00                                   5.43%                                      97.28%

9           DM0000000002         25.00                                   2.72%                                      100.00%



(9 行受影响)

drop table t_sum_abc

回复赞

sql_bd 2010-10-11

select *,
金额所占份额=ltrim(cast(amount*100.0/(select sum(amount) from t_sum_abc) as dec(18,2)))+'%',
金额累计份额=ltrim(cast((select sum(amount) from t_sum_abc where num<=t.num)*100.0/(select sum(amount) from t_sum_abc) as dec(18,2)))+'%'
from t_sum_abc t

回复赞

SQLCenter 2010-10-11

with t1 as

(

select ROW_NUMBER() over(order by amount desc) num ,material,amount, total=sum(amount)over(partition by getdate())

from(

	select 'DM0000000001' material,convert(float,100) amount

	union all

	select 'DM0000000002',25

	union all

	select 'DM0000000003',80

	union all

	select 'DM0000000004',200

	union all

	select 'DM0000000005',250

	union all

	select 'DM0000000006',50

	union all

	select 'DM0000000007',75

	union all

	select 'DM0000000008',60

	union all

	select 'DM0000000009',80

	) a

),

t2 as

(

	select *, total1=(select sum(amount) from t1 where num<=t.num) from t1 t

)

select num, material, amount, ltrim(convert(numeric(5,2),amount*100/total))+'%', ltrim(convert(numeric(5,2),total1*100/total))+'%' from t2

回复赞

liangCK 2010-10-11

create table t_sum_abc(num int,material varchar(20),amount numeric(9,2))



insert t_sum_abc

select ROW_NUMBER() over(order by amount desc) num ,material,amount

from(

    select 'DM0000000001' material,100 amount

    union all

    select 'DM0000000002',25

    union all

    select 'DM0000000003',80

    union all

    select 'DM0000000004',200

    union all

    select 'DM0000000005',250

    union all

    select 'DM0000000006',50

    union all

    select 'DM0000000007',75

    union all

    select 'DM0000000008',60

    union all

    select 'DM0000000009',80

) a



select *,CAST(amount*100./(select sum(amount) from t_sum_abc) AS numeric(10,2)) as 金额所占份额,

    CAST((SELECT SUM(amount) FROM t_sum_abc WHERE num<=A.num)*100./(select sum(amount) from t_sum_abc) AS numeric(10,2)) AS 金额累计份额

from t_sum_abc AS A



drop table t_sum_abc