1、问题：数据库中有个表（其中Price表示单价，Material_Count表示数量）
Price Material_Count
50.00 100
10.00 100
1.00 10000
500.00 100
1.00 500
10.00 500
5.00 500
5.00 500
5.00 500
2.00 200
200.00 100
200.00 400
20.00 400
2、希望通过SQL语句得到如下结果：
单价 数量 金额 占比 累计占比
200.00 400 80000.00 0.4268 0.4268
500.00 100 50000.00 0.2668 0.6936
200.00 100 20000.00 0.1067 0.8003
1.00 10000 10000.00 0.0533 0.8536
20.00 400 8000.00 0.0426 0.8962
50.00 100 5000.00 0.0266 0.9228
10.00 500 5000.00 0.0266 0.9494
5.00 500 2500.00 0.0133 0.9627
5.00 500 2500.00 0.0133 0.976
5.00 500 2500.00 0.0133 0.9893
10.00 100 1000.00 0.0053 0.9946
1.00 500 500.00 0.0026 0.9972
2.00 200 400.00 0.0021 0.9993
（金额=单价*数量； 占比=金额/SUM(金额); 累计占比=占比的累计求和）
3、本人写的SQL语句 大家觉得有没有改进之处：
with T as
(
SELECT Price,
Material_Count,
Price*Material_Count AS TotalPrice,
SUM(Price*Material_Count) OVER() AS Total,
Price*Material_Count/SUM(Price*Material_Count) OVER() AS Share,
ROW_NUMBER() OVER(ORDER BY Price*Material_Count DESC) AS ROW
FROM [Order]
)
SELECT Price,
Material_Count,
TotalPrice,
Share,
(select SUM(Share) from T b where b.ROW<=a.ROW) as Cum
FROM T a
------解决思路----------------------
DECLARE @sql NUMERIC(18,8),@sql2 NUMERIC(18,8)
SET @sql=(SELECT SUM(Price*Material_Count) FROM tabname)
SET @sql2=(select SUM((Price*Material_Count)/@sql) AS Share FROM tabname)
SELECT
Price,
Material_Count,
Price*Material_Count AS TotalPrice,
Price*Material_Count/@sql AS Share,
((Price*Material_Count)/@sql)/@sql2
FROM tabname
------解决思路----------------------
不能直接累加Share，要累加TotalPrice再做除非，否则最后不为1。
WITH T AS (
SELECT Price,
Material_Count,
Price*Material_Count AS TotalPrice,
SUM(Price*Material_Count) OVER() AS Total,
Price*Material_Count/SUM(Price*Material_Count) OVER() AS Share,
ROW_NUMBER() OVER(ORDER BY Price*Material_Count DESC) AS ROW
FROM [Order]
)
,R AS (
SELECT *,
CONVERT(money,TotalPrice) RowTotal,
Share Cum0,
CONVERT(float,TotalPrice / Total) Cum
FROM T
WHERE ROW = 1
UNION ALL
SELECT T.*,
CONVERT(money,R.RowTotal + T.TotalPrice),
R.Cum0 + T.Share,
CONVERT(float,(R.RowTotal + T.TotalPrice) / T.Total)
FROM R
JOIN T
ON R.ROW + 1 = T.ROW
)
SELECT Price,
Material_Count,
TotalPrice,
Share,
Cum0,
Cum
FROM R
Price Material_Count TotalPrice Share Cum0 Cum
--------- -------------- ----------- -------------------------- -------------------------- ----------------------
200.00 400 80000.00 0.4268943436499466382070 0.4268943436499466382070 0.426894343649947
500.00 100 50000.00 0.2668089647812166488794 0.6937033084311632870864 0.693703308431163
200.00 100 20000.00 0.1067235859124866595517 0.8004268943436499466381 0.80042689434365
1.00 10000 10000.00 0.0533617929562433297758 0.8537886872998932764139 0.853788687299893
20.00 400 8000.00 0.0426894343649946638207 0.8964781216648879402346 0.896478121664888
50.00 100 5000.00 0.0266808964781216648879 0.9231590181430096051225 0.92315901814301
10.00 500 5000.00 0.0266808964781216648879 0.9498399146211312700104 0.949839914621131
5.00 500 2500.00 0.0133404482390608324439 0.9631803628601921024543 0.963180362860192
5.00 500 2500.00 0.0133404482390608324439 0.9765208110992529348982 0.976520811099253
5.00 500 2500.00 0.0133404482390608324439 0.9898612593383137673421 0.989861259338314
10.00 100 1000.00 0.0053361792956243329775 0.9951974386339381003196 0.995197438633938
1.00 500 500.00 0.0026680896478121664887 0.9978655282817502668083 0.99786552828175
2.00 200 400.00 0.0021344717182497331910 0.9999999999999999999993 1