求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%
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%
解决方案 »
- 请教一个小问题
- 动态sql!!高手执教!!
- 关于varChar和Nvarchar类型在困惑
- 求解一个SQL问题.
- 一般的少文字字段用什么格式?用char吗?多文字的呢?还是都用varchar呢?
- 补分 * 7761098 navy887 两位小牛,刚才我错了,我在其他数据库中执行proc了,浪费了您们表情了
- 如何生成自动编号列?
- sqlserver2005 访问sqlserver2000报错:用户 '(null)' 登录失败。原因: 未与信任 SQL Server 连接相关联。
- 如何通过spid查看该进程曾经执行过的Sql语句?
- 触发器编译不过去啊,帮我看看啊
- 打开sql2008出现错误, 未能加载包 microsoft sql management studio package
- vs2008 连接 sql2008 问题
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
) aselect *,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 Adrop table t_sum_abc
(
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
金额所占份额=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
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 tnum 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
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