根据列合计取百分比，想破奶头没想明白，求教！（在线）

[徐徐透之]

现有如图，销售额根据从高到低排序，销售额合计：10000，根据合计80%（取从首行高到低合计8000）为A，80%-95%（取从首行高到低合计1500）为B，95%-100%（取从首行高到低合计500）为C，
然后根据百分比，来划分“分类值”
达到如下图：不知可否达到，求教！！！

[道素]

参考

    ;WITH a(goodno,soldamount) AS (
    	SELECT '1011',2000 UNION ALL
    	SELECT '1010',1800 UNION ALL
    	SELECT '1009',1600 UNION ALL
    	SELECT '1008',1400 UNION ALL
    	SELECT '1007',1200 UNION ALL
    	SELECT '1006',800 UNION ALL
    	SELECT '1005',700 UNION ALL
     	SELECT '1004',300 UNION ALL
    	SELECT '1003',150 UNION ALL
    	SELECT '1002',50    	
    ),b AS (
    SELECT ROW_NUMBER()OVER(ORDER BY a.soldamount DESC) AS Seq, SUM(a.soldamount)OVER(PARTITION BY 1) AS TotalAmount,*  FROM a
    )
    SELECT *,CASE  WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 0 AND 80 THEN 'A'
                         WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 80 AND 95 THEN 'B'
                         WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 95 AND 100 THEN 'C'
                         ELSE 'D' END AS Grade
    FROM b
    OUTER APPLY(SELECT SUM(lb.soldamount) AS prevTotal FROM b AS lb WHERE lb.seq<b.seq ) t


/*
Seq	TotalAmount	goodno	soldamount	prevTotal	Grade
1	10000	1011	2000	NULL	A
2	10000	1010	1800	2000	A
3	10000	1009	1600	3800	A
4	10000	1008	1400	5400	A
5	10000	1007	1200	6800	A
6	10000	1006	800	8000	B
7	10000	1005	700	8800	B
8	10000	1004	300	9500	C
9	10000	1003	150	9800	C
10	10000	1002	50	9950	C
*/

[顾西昂]

写个游标吧 一个加上去 不动脑子 方便轻盈

[徐徐透之]

谢谢，我理解理解看行不行，因为实际会有上万条数据

引用 2 楼 ch21st 的回复:

参考

    ;WITH a(goodno,soldamount) AS (
    	SELECT '1011',2000 UNION ALL
    	SELECT '1010',1800 UNION ALL
    	SELECT '1009',1600 UNION ALL
    	SELECT '1008',1400 UNION ALL
    	SELECT '1007',1200 UNION ALL
    	SELECT '1006',800 UNION ALL
    	SELECT '1005',700 UNION ALL
     	SELECT '1004',300 UNION ALL
    	SELECT '1003',150 UNION ALL
    	SELECT '1002',50    	
    ),b AS (
    SELECT ROW_NUMBER()OVER(ORDER BY a.soldamount DESC) AS Seq, SUM(a.soldamount)OVER(PARTITION BY 1) AS TotalAmount,*  FROM a
    )
    SELECT *,CASE  WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 0 AND 80 THEN 'A'
                         WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 80 AND 95 THEN 'B'
                         WHEN ROUND( (b.soldamount+ISNULL(t.prevTotal,0))*1.0/b.TotalAmount,4) *100 BETWEEN 95 AND 100 THEN 'C'
                         ELSE 'D' END AS Grade
    FROM b
    OUTER APPLY(SELECT SUM(lb.soldamount) AS prevTotal FROM b AS lb WHERE lb.seq<b.seq ) t


/*
Seq	TotalAmount	goodno	soldamount	prevTotal	Grade
1	10000	1011	2000	NULL	A
2	10000	1010	1800	2000	A
3	10000	1009	1600	3800	A
4	10000	1008	1400	5400	A
5	10000	1007	1200	6800	A
6	10000	1006	800	8000	B
7	10000	1005	700	8800	B
8	10000	1004	300	9500	C
9	10000	1003	150	9800	C
10	10000	1002	50	9950	C
*/