MS SQL 挑战问题

群里面有位网友提出了这样一个SQL挑战问题（原话不是这样，为了说明问题，我略做调整些）：

问题说明如下

有一条个销售报表TEST ：

商品 金额

A 1400

B 800

C 790

... ...

现在有这样一个需求（要写一个SQL取出如下数据）：先按销售金额倒叙排序，然后从这个报表取出前N条记录，并且这N条记录的销售金额之和是总金额的80%（<= 80%), 80%将作为一个查询条件（有可能是20%,30%）.他的想法是用嵌套函数 递归做法记录砍掉一半判断是否达到百分比 如果不足再取后半部分的一半 类似这样的方法；还有就是逐行金额相加当金额到达80%的时候 记录就从头显示到该条的前一条。 现在他想有没有其他跟高效的方法来实现这个需求？

那么我看到这个问题的时候，觉得有嵌套函数递归处理的方法的效率肯定是最低的，毕竟SQL不擅长于做逻辑处理。那么有没有更好的方法来实现呢？大家在看后面方法的时候，可以思考一下有没有更好的方法，能够达到一击必杀的效果（一个SQL搞定）。欢迎大家探讨！

下面是我的一个解决思路和方法,我在按销售金额倒叙排列的时候，能否得到一个销售金额的累加值？然后把这个累加值除以销售总金额即得到累加金额占总金额的一个比例，然后我们再加上查询条件。即可解决问题。

新建一个测试表TEST

CREATE TABLE TEST
(
    PRODUCT_NAME CHAR(32), --商品名称
    SALE_AMOUNT FLOAT 　　 --销售金额
)

 

插入测试数据

Code Snippet

1. INSERT INTO TEST
2.  
3. SELECT 'A' ,13000 UNION ALL
4.  
5. SELECT 'A' ,12000 UNION ALL
6.  
7. SELECT 'A' ,9000 UNION ALL
8.  
9. SELECT 'B' ,167000 UNION ALL
10.  
11. SELECT 'B' ,137000 UNION ALL
12.  
13. SELECT 'B' ,107000 UNION ALL
14.  
15. SELECT 'C' ,78000 UNION ALL
16.  
17. SELECT 'C' ,12000;

实现销售金额的累加值字段的脚本（这个脚本效率没有测试，小量数据应该没有问题）

Code Snippet

1. SELECT ROW_NUMBER() OVER (ORDER BY SALE_AMOUNT DESC ) AS ROW,PRODUCT_NAME,
2.        T.SALE_AMOUNT,
3.          (SELECT SUM(SALE_AMOUNT)AS ACCUMATE_SALE FROM TEST WHERE T.SALE_AMOUNT <= SALE_AMOUNT ) AS ACCUMATE_SALE
4. FROM TEST T

(截图)

那么接下来我们来实现上面的思路

SELECT  ROW_NUMBER() OVER ( ORDER BY T.SALE_AMOUNT DESC ) AS ROW ,
        T.PRODUCT_NAME ,
        T.SALE_AMOUNT ,
        ( SELECT    SUM(SALE_AMOUNT) SUM_SALE
          FROM      TEST
          WHERE     T.SALE_AMOUNT <= SALE_AMOUNT
        ) / L.SALE_AMOUNT AS SUM_RAT
FROM    TEST T ,
        ( SELECT    SUM(SALE_AMOUNT) AS SALE_AMOUNT
          FROM      TEST
        ) L

                                     截图

接下来就水到渠成了

SELECT T.ROW, T.PRODUCT_NAME, T.SALE_AMOUNT, T.SUM_RAT FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY T.SALE_AMOUNT DESC) AS ROW,
       T.PRODUCT_NAME,
       T.SALE_AMOUNT,
       (SELECT SUM(SALE_AMOUNT) SUM_SALE
          FROM TEST
         WHERE T.SALE_AMOUNT <= SALE_AMOUNT) / L.SALE_AMOUNT AS SUM_RAT
  FROM TEST T, (SELECT SUM(SALE_AMOUNT) AS SALE_AMOUNT FROM TEST) L
) T WHERE SUM_RAT < 0.8

截图

后记总结：其实我们可以用SQL很巧妙的实现很多逻辑复杂的需求，避免我们去做大量复杂的逻辑处理，这就需求我们开动脑筋，挑战极限。像ITPUT就有很多SQL挑战问题http://www.itpub.net/forum.php?mod=forumdisplay&fid=3&filter=typeid&typeid=1808 ，国外网站例如http://www.plsqlchallenge.com/， 有兴趣的同学尽可去尝试一下。