# 求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 条回复

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,

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 *,

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

2.1w+

MS-SQL Server 疑难问题

2010-10-11 10:45