特难的SQL,请高书帮助,跪求各位大峡!该如何处理

特难的SQL,请高书帮助,急,跪求各位大峡!!!
我的表结构是这样的
itemcode 销售数量 次数 级别
00001 15 2  
00002 15 2  
00003 10 1  
00004 10 2  
00005 10 1  
00006 8 2  
00007 8 1  
00008 7 1  
00009 6 2  
00010 5 2  
00011 3 2  
00012 1 1  
00013 1 1  
00014 1 1  
我的要求是根据销售总数量进行汇总,然后根据销售数量的排序(desc)一条一条往下加,当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2，然后重新从下条开始累加，达到销售总数量36%时, 次数>1的级别为B1,等于1的级别为B2，达到销售总数量18%时, 次数>1的级别为C1,等于1的级别为C2，达到销售总数量6%时, 次数>1的级别为D1,等于1的级别为D2，
具体结果为:
itemcode 销售数量 次数 级别
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2  
00004 10 2 B1  
00005 10 1 B2  
00006 8 2 B1  
00007 8 1 B2  
00008 7 1 C2  
00009 6 2 C1  
00010 5 2 C1  
00011 3 2 D1  
00012 1 1 D2  
00013 1 1 D2  
00014 1 1 D2
   


[解决办法]
使用Oracle的分析函数（over,dense）。
[解决办法]
这可行吗? 
如果前面不是正好的40%，36%，比如41%，36%，18%，5%，那5%的部分将永远不可能被分类了
[解决办法]
这种需求用SQL能做,用程序写吧.
[解决办法]
这个要用存储过程去实现了
[解决办法]
直接使用分析函数肯定能做出来，但要累伤人的。

还上赞同使用存贮过程。

[解决办法]

SQL code

-- 好象也不复杂啊,发现规律就好办SQL> select * from test;ITEMCODE         SALS         CS JB---------- ---------- ---------- ----00001              15          2 00002              15          2 00003              10          1 00004              10          2 00005              10          1 00006               8          2 00007               8          1 00008               7          1 00009               6          2 00010               5          2 00011               3          2 00012               1          1 00013               1          1 00014               1          1 14 rows selectedSQL> with x as (select itemcode,sals,cs,sum(sals) over() sals_total,  2                    sum(sals) over(order by rownum)/sum(sals) over() sals_bfb  3              from  test  4              order by sals desc,itemcode)  5  select itemcode,sals,cs,  6         case when sals_bfb<=0.4 and cs=1 then 'A2'  7              when sals_bfb>0.4 and sals_bfb<=(0.4+0.36) and cs=1 then 'B2'  8              when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) and cs=1 then 'C2'  9              when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) and cs=1 then 'D2' 10              else (case when sals_bfb <= 0.4 then 'A1' 11                         when sals_bfb > 0.4 and sals_bfb <= (0.4+0.36) then 'B1' 12                         when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) then 'C1' 13                         when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) then 'D1' 14                         else 'E0' 15                         end) 16              end JB 17  from x 18  /ITEMCODE         SALS         CS JB---------- ---------- ---------- --00001              15          2 A100002              15          2 A100003              10          1 A200004              10          2 B100005              10          1 B200006               8          2 B100007               8          1 B200008               7          1 C200009               6          2 C100010               5          2 C100011               3          2 D100012               1          1 D200013               1          1 D200014               1          1 D214 rows selectedSQL>
[解决办法]
SQL code
--楼主根据实际条件再修改一下：SQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG-------- ------- --------- ----00001         15         2 00002         15         2 00003         10         1 00004         10         2 00005         10         1 00006          8         2 00007          8         1 00008          7         1 00009          6         2 00010          5         2 00011          3         2 00012          1         1 00013          1         1 00014          1         1 14 rows selectedSQL> SQL> create or replace procedure p_item_order as  2    itemcode  t_1123.itemcode%type;  3    itemsum   t_1123.itemsum%type;  4    itemcount t_1123.itemcount%type;  5    flag      t_1123.flag%type;  6    7    cursor c1 is  8      select count(*) from t_1123;  9   10    cursor c2 is 11      select itemcode, itemsum, itemcount 12        from t_1123 13       where itemcode = (select min(itemcode) 14                           from t_1123 15                          where flag is null 16                             or flag = ''); 17   18    cursor c3 is 19      select sum(itemsum) from t_1123 where flag is not null; 20   21    v_t1123    number(4) := 0; 22    v_sum      number(3) := 0; 23    v_count    number(3) := 0; 24    v_itemsum  number(3) := 0; 25    v_itemcode varchar2(5); 26   27  begin 28    open c1; 29    fetch c1 30      into v_t1123; 31    for i in 1 .. 3 loop 32      open c2; 33      fetch c2 34        into v_itemcode, v_sum, v_count; 35      open c3; 36      fetch c3 37        into v_itemsum; 38      if v_itemsum is null then 39        update t_1123 set flag = 'A1' where itemcode = v_itemcode; 40      end if; 41      if v_itemsum is not null then 42        update t_1123 43           set flag = 'A1' 44         where v_sum / v_itemsum >= 0.4 45           and v_count > 1 46           and itemcode = v_itemcode; 47      end if; 48      update t_1123 49         set flag = 'A2' 50       where v_sum / v_itemsum >= 0.2 51         and v_count = 1 52         and itemcode = v_itemcode; 53      commit; 54      close c2; 55      close c3; 56    end loop; 57    for i in 4 .. v_t1123 loop 58      open c2; 59      fetch c2 60        into v_itemcode, v_sum, v_count; 61      open c3; 62      fetch c3 63        into v_itemsum; 64      update t_1123 65         set flag = 'B1' 66       where v_sum / v_itemsum >= 0.36 67         and v_sum / v_itemsum < 0.4 68         and v_count > 1 69         and itemcode = v_itemcode; 70      update t_1123 71         set flag = 'B2' 72       where v_sum / v_itemsum >= 0.36 73         and v_sum / v_itemsum < 0.4 74         and v_count = 1 75         and itemcode = v_itemcode; 76      update t_1123 77         set flag = 'C1' 78       where v_sum / v_itemsum >= 0.18 79         and v_sum / v_itemsum < 0.36 80         and v_count > 1 81         and itemcode = v_itemcode; 82      update t_1123 83         set flag = 'C2' 84       where v_sum / v_itemsum >= 0.18 85         and v_sum / v_itemsum < 0.36 86         and v_count = 1 87         and itemcode = v_itemcode; 88      update t_1123 89         set flag = 'D1' 90       where v_sum / v_itemsum >= 0.06 91         and v_sum / v_itemsum < 0.18 92         and v_count > 1 93         and itemcode = v_itemcode; 94      update t_1123 95         set flag = 'D2' 96       where v_sum / v_itemsum >= 0.06 97         and v_sum / v_itemsum < 0.18 98         and v_count = 1 99         and itemcode = v_itemcode;100  101      commit;102  103      close c2;104      close c3;105    end loop;106    close c1;107  end;108  /Procedure createdSQL> exec p_item_order;PL/SQL procedure successfully completedSQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG-------- ------- --------- ----00001         15         2 A100002         15         2 A100003         10         1 A200004         10         2 C100005         10         1 C200006          8         2 D100007          8         1 D200008          7         1 D200009          6         2 D100010          5         2 00011          3         2 00012          1         1 00013          1         1 00014          1         1 14 rows selectedSQL>
[解决办法]
可能今天俺脑子不好使了，总共才100的数量，15也只占总数量的15％，为什么第一条记录是A1？呵呵...
当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2
引用楼主 tian790317 的帖子:

我的表结构是这样的 
itemcode        销售数量              次数                    级别 
00001            15                  2                                
00002            15                  2                                
00003            10                  1                                  
00004            10                  2                                  
00005           …

[解决办法]
楼主对４０％的界定不清楚，要看他的需求了。

顺便修正一下我９楼的SQL，把第二行的order by rownum 改为order by itemcode
[解决办法]
SQL code
--- 更新表，将上面代码套上就行了啊．SQL>  update test t1  2      set  3   jb = (select jb  4   from (  5   with x as (select itemcode,sals,cs,sum(sals) over() sals_total,  6                      sum(sals) over(order by rownum)/sum(sals) over() sals_bfb  7                from  test  8                order by sals desc,itemcode)  9    select itemcode,sals,cs, 10           case when sals_bfb<=0.4 and cs=1 then 'A2' 11                when sals_bfb>0.4 and sals_bfb<=(0.4+0.36) and cs=1 then 'B2' 12                when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) and cs=1 then 'C2' 13                when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) and cs=1 then 'D2' 14                else (case when sals_bfb <= 0.4 then 'A1' 15                           when sals_bfb > 0.4 and sals_bfb <= (0.4+0.36) then 'B1' 16                           when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) then 'C1' 17                           when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) then 'D1' 18                           else 'E0' 19                           end) 20                end JB 21    from x)t where t.itemcode = t1.itemcode) 22  /14 rows updatedSQL>
[解决办法]
探讨

不过还想问下这个语句是什么意思? 
  1    with x as (select itemcode,sals,cs,sum(sals) over() sals_total, 
  2                    sum(sals) over(order by rownum)/sum(sals) over() sals_bfb 
  3              from  test 
  4              order by sals desc,itemcode) 

[解决办法]
先顶了再说。。。
[解决办法]
顶
[解决办法]
1.LZ具体操作时，可使用ITEMCODE排好序的数据，如没有，则新建一下；
2.对不起，第一行的delete写错了不用。FLAG列有数据时，使用update t_1123 set flag=null; 以使此列数据初始化；
3.感谢高手使此贴加精，本人同样感动中......


探讨

SQL code
--给楼主整出来了，如果原来有数据的话，可以删除flag列中的内容（字段比照一下即可）：


delete from t_1123 where flag=null;
commit;

SQL> select * from t_1123;

ITEMCODE ITEMSUM ITEMCOUNT FLAG
-------- ------- --------- ----
00001         15         2 
00002         15         2 
00003         10         1 
00004         10         2 
00005         10         1 
00006      …

[解决办法]
oracle ,实现，学习下？
[解决办法]
都是强人，佩服。学习中~~~
[解决办法]
可以用程序实现！
[解决办法]
探讨

咦~~? 

难得一见oracle版有精华帖啊．顶～

[解决办法]
学习！
[解决办法]
SELECT ITEMCODE,
      SALS,
      CS,
      CASE
        WHEN SALS_BFB <= 0.4 THEN
         DECODE(CS, 1, 'A2', 'A1')
        WHEN SALS_BFB > 0.4 AND SALS_BFB <= (0.4 + 0.36) THEN
         DECODE(CS, 1, 'B2', 'B1')
        WHEN SALS_BFB > (0.4 + 0.36) AND SALS_BFB <= (0.4 + 0.36 + 0.18) THEN
         DECODE(CS, 1, 'C2', 'C1')
        ELSE
         DECODE(CS, 1, 'D2', 'D1')
      END JB
 FROM (SELECT ITEMCODE,
              SALS,
              CS,
              SUM(SALS) OVER() SALS_TOTAL,
              SUM(SALS) OVER(ORDER BY ROWNUM) / SUM(SALS) OVER() SALS_BFB
         FROM TEST
        ORDER BY SALS DESC, ITEMCODE);
[解决办法]
如果就数据量少的话,比如只有两条数据的话,按照这种分是不可能统计出级别的,
还有就是销售数量相差太大的话,也可能按照楼主这种分是不可能统计出的!!
[解决办法]
mark
[解决办法]
mark
[解决办法]
我靠，怎么这么多！
[解决办法]
噢，好贴
[解决办法]
写的不好，大家见笑了
2000写的
SELECT     t1.id, Table_1_1.SALS,  
                     CASE WHEN t1.c1 <= 0.4 THEN 'A' WHEN t1.c1 <= 0.4 + 0.36 THEN 'B' WHEN t1.c1 <= 0.4 + 0.36 + 0.18 THEN 'c' ELSE 'D' END + CASE Table_1_1.csjb WHEN
                      1 THEN '2' ELSE '1' END AS Expr2
FROM         (SELECT     CAST(a_1.Expr1 AS float) / b_1.Expr1 AS c1, a_1.id
                      FROM          (SELECT     SUM(b.SALS) AS Expr1, a.id
                                              FROM          Table_1 AS a LEFT OUTER JOIN
                                                                     Table_1 AS b ON a.id >= b.id
                                              GROUP BY a.id) AS a_1 CROSS JOIN
                                                 (SELECT     SUM(SALS) AS Expr1
                                                   FROM          Table_1) AS b_1) AS t1 INNER JOIN
                     Table_1 AS Table_1_1 ON Table_1_1.id = t1.id
[解决办法]
高手，学习了！



探讨

SQL code-- 好象也不复杂啊,发现规律就好办

SQL> select * from test;

ITEMCODE         SALS         CS JB
---------- ---------- ---------- ----
00001              15          2 
00002              15          2 
00003              10          1 
00004              10          2 
00005              10          1 
00006               8          2 
00007               8          1 
00008…

[解决办法]
受打击了,什么时候有这种水平
[解决办法]
受非常嚴懲的打擊，你們都是強人俺幾時才能達這種水平
[解决办法]
MARK


PS:  
我的目标是 ----> 

^_^
[解决办法]
MARK
[解决办法]
用移动窗口分析函数写了一下,基于数据字典表进行的测试,如下:就你的需求来看,partition by可以免去
SELECT  
 Y.*,
 level_prefix || to_char(DENSE_RANK() over(PARTITION BY table_name,level_prefix ORDER BY AVG_COL_LEN))
 FROM  
(
 SELECT x.*,
   (CASE WHEN accumulative_percent<=0.4 THEN 'A'
       WHEN accumulative_percent>0.4 AND accumulative_percent<=(0.4+0.36) THEN 'B'
       WHEN accumulative_percent>(0.4+0.36) AND accumulative_percent<=(0.4+0.36 +0.18) THEN 'C'
       WHEN accumulative_percent>(0.4+0.36+0.18) THEN 'D'
   END) level_prefix   
 FROM
 (
 SELECT  
   table_name,column_name,num_distinct,AVG_COL_LEN,
   row_number() over(PARTITION BY table_name ORDER BY num_distinct DESC),
   SUM(num_distinct) over(PARTITION BY table_name ORDER BY num_distinct DESC ROWS UNBOUNDED PRECEDING),
   SUM(num_distinct) over(PARTITION BY table_name),
   SUM(num_distinct) over(PARTITION BY table_name ORDER BY num_distinct DESC ROWS UNBOUNDED PRECEDING)/
   SUM(num_distinct) over(PARTITION BY table_name) accumulative_percent
 FROM user_tab_col_statistics
 WHERE table_name='CIRCUIT'
 ) x
) Y
对应实现你的需求为如下语句:

SELECT 
  Y.*,
  level_prefix || to_char(DENSE_RANK() over(PARTITION BY level_prefix ORDER BY 次数))
  FROM 
(
  SELECT x.*,
    (CASE WHEN accumulative_percent<=0.4 THEN 'A'
        WHEN accumulative_percent>0.4 AND accumulative_percent<=(0.4+0.36) THEN 'B'
        WHEN accumulative_percent>(0.4+0.36) AND accumulative_percent<=(0.4+0.36 +0.18) THEN 'C'
        WHEN accumulative_percent>(0.4+0.36+0.18) THEN 'D'
    END) level_prefix    
  FROM
  (
  SELECT 
    itemcode,销售数量,次数,
    row_number() over(ORDER BY 销售数量 DESC),
    SUM(销售数量) over(ORDER BY 销售数量 DESC ROWS UNBOUNDED PRECEDING),
    SUM(销售数量) over(),
    SUM(销售数量) over(ORDER BY 销售数量 DESC ROWS UNBOUNDED PRECEDING)/SUM(销售数量) over() accumulative_percent
  FROM your_table
  ) x
) Y