如何优化一下改代码

[zhangzhikai397]

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

[Pact_Alice]

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

[Tiger_Zhao]

不能直接累加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

[zhangzhikai397]

不错 ，谢谢大家

[gw6328]

楼主的已经很简化了。