有如下记录
SO_NUM   ITEM_NUM      SET_NUM 
1001     AAAA          1001
1001     AAAA          1001
1001     AAAA          1001
1001     BBBB          2222
1001     BBBB          2222
1001     CCCC          1234
1001     CCCC          1234
1001     CCCC          12341002     ABCD          1008
1002     ABCD          1008
1002     ABCD          1008
1002     CDEF          2005
1002     CDEF          2005
1002     EEEE          4001
1002     EEEE          4001
1002     EEEE          40011003     WWWW          1100
1003     WWWW          1100
1003     GGGG          1111
1003     GGGG          1111
1003     GGGG          1111
......现在要获得如下格式,其sql语句该如何写?
SO_NUM   ITEM_NUM      SET_NUM   GROUP_NUM
1001     AAAA          1001       1
1001     AAAA          1001       1
1001     AAAA          1001       1
1001     BBBB          2222       2
1001     BBBB          2222       2
1001     CCCC          1234       3
1001     CCCC          1234       3
1001     CCCC          1234       31002     ABCD          1008       1
1002     ABCD          1008       1
1002     ABCD          1008       1
1002     CDEF          2005       2
1002     CDEF          2005       2
1002     EEEE          4001       3
1002     EEEE          4001       3
1002     EEEE          4001       31003     WWWW          1100       1
1003     WWWW          1100       1
1003     GGGG          1111       2
1003     GGGG          1111       2
1003     GGGG          1111       2
......

解决方案 »

  1.   

    SELECT SO_NUM, ITEM_NUM, SET_NUM, 
           DENSE_RANK() OVER(ORDER BY SO_NUM,ITEM_NUM) AS GROUP_NUM
    FROM  YOURTABLENAME
      

  2.   

    二楼的不对,变成了
    SO_NUM   ITEM_NUM      SET_NUM   GROUP_NUM
    1001     AAAA          1001       1
    1001     AAAA          1001       2
    1001     AAAA          1001       3
    1001     BBBB          2222       4
    1001     BBBB          2222       5
    1001     CCCC          1234       6
    1001     CCCC          1234       7
    1001     CCCC          1234       81002     ABCD          1008       9
    1002     ABCD          1008       10
    1002     ABCD          1008       11
    1002     CDEF          2005       12
    1002     CDEF          2005       13
    1002     EEEE          4001       14
    1002     EEEE          4001       15
    1002     EEEE          4001       161003     WWWW          1100       17
    1003     WWWW          1100       18
    1003     GGGG          1111       19
    1003     GGGG          1111       20
    1003     GGGG          1111       21
      

  3.   

    首先谢谢各位,
    但是我这里有两个分组,一个是SO_NUM,另一个是SO_NUM,ITEM_NUM,SET_NUM,
    测试了一下,二楼的结果为
    SO_NUM   ITEM_NUM      SET_NUM   GROUP_NUM(应该为)
    1001     AAAA          1001       1
    1001     AAAA          1001       1
    1001     AAAA          1001       1
    1001     BBBB          2222       2
    1001     BBBB          2222       2
    1001     CCCC          1234       3
    1001     CCCC          1234       3
    1001     CCCC          1234       31002     ABCD          1008       4 (1)
    1002     ABCD          1008       4 (1)
    1002     ABCD          1008       4 (1)
    1002     CDEF          2005       5 (2)
    1002     CDEF          2005       5 (2)
    1002     EEEE          4001       6 (3)
    1002     EEEE          4001       6 (3)
    1002     EEEE          4001       6 (3)1003     WWWW          1100       7 (1)
    1003     WWWW          1100       7 (1)
    1003     GGGG          1111       8 (2)
    1003     GGGG          1111       8 (2)
    1003     GGGG          1111       8 (2)

      

  4.   

    你这个表中的数据为什么要排序成你要求的样子,规律何在?以SO_NUM分组没错,但是是以ITEM_NUM排序,还是SET_NUM排序呢?以我目前的水平,觉得比较混乱。我暂时写出这个语句
    SQL> select so_num,item_num,set_num,
      2  dense_rank()over(partition by so_num order by item_num,set_num)cn
      3  from kkk;SO_NUM               ITEM_NUM             SET_NUM                      CN
    -------------------- -------------------- -------------------- ----------
    1001                 AAAA                 1001                          1
    1001                 AAAA                 1001                          1
    1001                 AAAA                 1001                          1
    1001                 BBBB                 2222                          2
    1001                 BBBB                 2222                          2
    1001                 CCCC                 1234                          3
    1001                 CCCC                 1234                          3
    1001                 CCCC                 1234                          3
    1002                 ABCD                 1008                          1
    1002                 ABCD                 1008                          1
    1002                 ABCD                 1008                          1SO_NUM               ITEM_NUM             SET_NUM                      CN
    -------------------- -------------------- -------------------- ----------
    1002                 CDEF                 2005                          2
    1002                 CDEF                 2005                          2
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1003                 GGGG                 1111                          1
    1003                 GGGG                 1111                          1
    1003                 GGGG                 1111                          1
    1003                 WWWW                 1100                          2
    1003                 WWWW                 1100                          2已选择22行。是以item_num派讯,但是最后两行数据顺序有误;以set_num排序,结果如下,也有问题
    SQL> select so_num,item_num,set_num,
      2  dense_rank()over(partition by so_num order by set_num)cn from kkk;SO_NUM               ITEM_NUM             SET_NUM                      CN
    -------------------- -------------------- -------------------- ----------
    1001                 AAAA                 1001                          1
    1001                 AAAA                 1001                          1
    1001                 AAAA                 1001                          1
    1001                 CCCC                 1234                          2
    1001                 CCCC                 1234                          2
    1001                 CCCC                 1234                          2
    1001                 BBBB                 2222                          3
    1001                 BBBB                 2222                          3
    1002                 ABCD                 1008                          1
    1002                 ABCD                 1008                          1
    1002                 ABCD                 1008                          1SO_NUM               ITEM_NUM             SET_NUM                      CN
    -------------------- -------------------- -------------------- ----------
    1002                 CDEF                 2005                          2
    1002                 CDEF                 2005                          2
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1002                 EEEE                 4001                          3
    1003                 WWWW                 1100                          1
    1003                 WWWW                 1100                          1
    1003                 GGGG                 1111                          2
    1003                 GGGG                 1111                          2
    1003                 GGGG                 1111                          2已选择22行。
      

  5.   

    SELECT SO_NUM, ITEM_NUM, SET_NUM, 
           DENSE_RANK() OVER(PARTITION BY SO_NUM ORDER BY SO_NUM,ITEM_NUM) AS GROUP_NUM
    FROM TEST_SONUM下次把需求写清楚,不然也不知道你到底想要达到什么效果
      

  6.   

    求语句啥时候是个头,看看这篇文章,或许你就不用再向他人求了。大家的态度还不好。
    http://blog.csdn.net/precipitant/archive/2005/08/03/445022.aspx
      

  7.   

    聚合函数最简单的应用:
    SELECT SO_NUM, ITEM_NUM, SET_NUM, 
           RANK() OVER(PARTITION BY SO_NUM ORDER BY ITEM_NUM) AS GROUP_NUM
    FROM TEST_SONUM
      

  8.   

    precipitant(塞北的雪) ( ) 信誉:100    Blog   加为好友  2007-04-24 13:38:57  得分: 0  
     
     
       求语句啥时候是个头,看看这篇文章,或许你就不用再向他人求了。大家的态度还不好。
    http://blog.csdn.net/precipitant/archive/2005/08/03/445022.aspx
    ===============================================================================
    大家的态度还不好????
    LS的,
    RANK能达到这效果吗?拜托你下次Ctrl+C, Ctrl+V的时候先自己研究一下后再做
    最讨厌这种Ctrl+C Ctrl+V后跟风的做法,跟风跟对了还差不多,偏偏又跟出个错的-_-DENSE_RANK的效果:
        SO_NUM ITEM_NUM    SET_NUM  GROUP_NUM
    ---------- -------- ---------- ----------
          1001 AAAA           1001          1
          1001 AAAA           1001          1
          1001 AAAA           1001          1
          1001 BBBB           2222          2
          1001 BBBB           2222          2
          1001 CCCC           1234          3
          1001 CCCC           1234          3
          1001 CCCC           1234          3RANK的效果
        SO_NUM ITEM_NUM    SET_NUM  GROUP_NUM
    ---------- -------- ---------- ----------
          1001 AAAA           1001          1
          1001 AAAA           1001          1
          1001 AAAA           1001          1
          1001 BBBB           2222          4
          1001 BBBB           2222          4
          1001 CCCC           1234          6
          1001 CCCC           1234          6
          1001 CCCC           1234          6自己体会一下去吧.
      

  9.   

    见笑。没注意!
    dense_rank()