sql - 通过对表中所有记录的日期重新排序来更新字段

Question

目前，我有此记录

发票 list 表

InvoiceID   StoreCustomerID IssuedDate Amount      IsPenalty   EmployeeID
----------- --------------- ---------- ----------- ----------- -----------
1           13              2007-01-12 244         0           41
2           31              2007-04-05 81          0           34
3           23              2007-01-09 184         0           46
4           28              2007-11-21 231         0           17
5           36              2006-09-19 121         0           22
6           28              2006-10-24 240         0           17
7           15              2006-12-11 193         0           47
8           21              2007-01-15 172         0           4

InvoiceID 自动递增。我想要做的是通过增加上一行的日期来更新 IssuedDate 。我想像这样更新

InvoiceID   StoreCustomerID IssuedDate Amount      IsPenalty   EmployeeID
----------- --------------- ---------- ----------- ----------- -----------
1           13              2007-01-12 244         0           41
2           31              2007-01-13 81          0           34
3           23              2007-01-14 184         0           46
4           28              2007-01-15 231         0           17
5           36              2007-01-16 121         0           22
6           28              2007-01-17 240         0           17
7           15              2007-01-18 193         0           47
8           21              2007-01-19 172         0           4

目前我有这个选择语句并且运行良好。但我如何用它来更新 IssuedDate ？

WITH SequenceDate AS
(
    SELECT  *, ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber
    FROM    Invoice
)
SELECT RowNumber, DATEADD(d, RowNumber - 1, b.IssuedDate)
FROM SequenceDate
ORDER BY RowNumber

更新1

我对第一篇文章感到非常抱歉，因为给我的指示不正确。日期不应增加，因为我们不允许更改表中的记录，除非我们只能按升序重新排列日期。应该是这样。

InvoiceID   StoreCustomerID IssuedDate Amount      IsPenalty   EmployeeID
----------- --------------- ---------- ----------- ----------- -----------
1           13              2006-09-19 244         0           41
2           31              2006-10-24 81          0           34
3           23              2006-12-11 184         0           46
4           28              2007-01-09 231         0           17
5           36              2007-01-12 121         0           22
6           28              2007-01-15 240         0           17
7           15              2007-04-05 193         0           47
8           21              2007-11-21 172         0           4

Answer 1

如果您知道序列中的第一个日期，您可以简单地向其中添加 RowNumber:

; WITH SequenceDate AS
(
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber,
            MIN(IssuedDate) over () FirstDate
    FROM    Invoice
)
UPDATE SequenceDate
   SET IssuedDate = DATEADD(d, RowNumber - 1, FirstDate)

Here is Sql Fiddle with example .

更新:

to match first question's output exactly :

; WITH SequenceDate AS
(
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY InvoiceID) RowNumber
    FROM    Invoice
)
UPDATE SequenceDate
   SET IssuedDate = DATEADD(d, RowNumber - 1, 
                           (select IssuedDate
                              from Invoice
                             where InvoiceID = 1))

And to rearange dates to follow InvoiceID :

; WITH SequenceDate AS
(
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY InvoiceID) RowNumber,
            ROW_NUMBER() OVER (ORDER BY IssuedDate) DateNumber
    FROM    Invoice
)
UPDATE SequenceDate
   SET IssuedDate = d.IssuedDate
  from SequenceDate d
 where SequenceDate.RowNumber = d.DateNumber