开发者

How to assign correlative numbers to rows only using SQL?

I have the following table in an Oracle database:

InvoiceNumber InvoiceDate InvoiceCorrelative
------------- ----------- ------------------
       开发者_运维问答   123  02-03-2009                  0
          124  02-03-2009                  0
          125  02-04-2009                  0
          126  02-04-2009                  0
          127  02-04-2009                  0
          128  02-05-2009                  0
          129  02-06-2009                  0
          130  02-06-2009                  0
          ...         ...                ...

And I want to set a value for the InvoiceCorrelative column in every row in order to have a sequence of numbers starting at 1 for each date. In the example above I want the table to look like this:

InvoiceNumber InvoiceDate InvoiceCorrelative
------------- ----------- ------------------
          123  02-03-2009                  1
          124  02-03-2009                  2
          125  02-04-2009                  1
          126  02-04-2009                  2
          127  02-04-2009                  3
          128  02-05-2009                  1
          129  02-06-2009                  1
          130  02-06-2009                  2
          ...         ...                ...

Is it possible to do it only using SQL statements?. I've been playing with rownum but didn't get anywhere.


Try:

ROW_NUMBER() OVER (PARTITION BY InvoiceDate ORDER BY InvoiceNumber)


Using Standard SQL,

  Update TableName T Set
    InvoiceCorrelative = 
     (Select Count(*) From TableName 
      Where InvoiceDate = T.InvoiceDate
        And InvoiceNumber <= T.InvoiceNumber)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜