求一个对表中记录分组的存储过程

Question

如有如下的表和记录
----------------------------------
 id  |  count  | thread
---------------------------------
  1      4      
  2      5    
  4      7
  9      10
  5      5
  6      8
  7      7
----------------------------------
现在把所有的记录分成3组，要求每组的count之和大致相当
可能的结果如下：
----------------------------------
 id  |  count  | thread
---------------------------------
  1      4         1
  2      5         1
  4      7         1
  9      10        2
  5      5         2
  6      8         3
  7      7         3
----------------------------------
现在要把这个过程写成一个存储过程（其中组数作为一个参数传入）。
请各位高手提供效率尽可能高的实现或算法。分数可再开贴给

Answer 1

只会分组....SELECT id ,count, 
   NTILE(3) (ORDER BY id DESC NULLS FIRST) AS thread 
FROM table1

Answer 2

SQL> select * from t;        ID        COU
---------- ----------
         6          3
         7          5
         8          5
         9          5
        10          5
        11          5
        12          5
        13          5
        14          5
        15          6已选择10行。已用时间:  00: 00: 00.61
SQL> select id,cou,ceil(rownum/((count(*) over())/3)) from t;        ID        COU CEIL(ROWNUM/((COUNT(*)OVER())/3))
---------- ---------- ---------------------------------
         6          3                                 1
         7          5                                 1
         8          5                                 1
         9          5                                 2
        10          5                                 2
        11          5                                 2
        12          5                                 3
        13          5                                 3
        14          5                                 3
        15          6                                 3已选择10行。已用时间:  00: 00: 00.60
SQL>

Answer 3

我用我的数据运行了一下你的是这样的结果，并没有显示出有按值相加差不多的分组的样子啊？
        ID      PRICE CEIL(ROWNUM/((COUNT(*)OVER())/
---------- ---------- ------------------------------
         1          2                              1
         2          3                              1
         3          7                              2
         4          8                              2
         5          1                              2
         6         10                              3
         7          7                              3
         8          4                              3

Answer 4

SELECT id ,count, 
   NTILE(3) over(ORDER BY id DESC NULLS FIRST) AS thread 
FROM table1

Answer 5

加上ceilSQL> select id,cou,ceil(rownum/ceil(((count(*) over())/3))) from t;        ID        COU CEIL(ROWNUM/CEIL(((COUNT(*)OVER())/3)))
---------- ---------- ---------------------------------------
         6          3                                       1
         7          5                                       1
         8          5                                       1
         9          5                                       2
        10          5                                       2
        11          5                                       2
        12          5                                       3
        13          5                                       3已选择8行。已用时间:  00: 00: 00.50
SQL>

Answer 6

ceil(rownum/ceil(((count(*) over())/3))) 这一段里面就没有一个字跟count那一列的值相关，难以想象可以达到楼主的要求....你写的这么长一串就是实现了NTILE这个函数的功能嘛

Answer 7

我要的是一个存储过程。根据分组把thread字段添上

Answer 8

sasacat(傻傻猫) 的改一下就可以了，LZ的结果并没有排序：SQL> SELECT id ,count, NTILE(3) over(order by 1) AS thread FROM a;       ID  COUNT            THREAD
---------- ---------- ----------
         1 4                   1
         2 5                   1
         4 7                   1
         9 10                  2
         5 5                   2
         6 8                   3
         7 7                   37 rows selected

Answer 9

上面的朋友可能没有理解我的意思：
       ID  COUNT            THREAD
---------- ---------- ----------
         1 4                   1
         2 5                   1
         4 7                   1
         9 10                  2
         5 5                   2
         6 8                   3
         7 7                   3
这样分是因为 4+5+7 ~= 10+5 ~= 8+7 

Answer 10

建一个按COUNT列排序的游标，从小到大给每行依次分配123123123....我估计这个算法能凑合：)不过代码就懒得写了，楼主好歹自己动动手吧

Answer 11

感觉实现楼主的想法不能达到理想的状态， sasacat(傻傻猫) 的提议不错。

Answer 12

----------------------------------
 id  |  count  | thread
---------------------------------
  1      4         1
  2      5         2
  4      7         3
  9      10        2
  5      5         1
  6      8         3
  7      7         1
----------------------------------这种状态也符合楼主要求，那么楼主需要的结果就不具备唯一性，所以个人认为没有实现的含义。