TSQL基于另一列自增并重新开始计数

Question

我有一个如下的表格：

colA   colB(unique)   colC
---------------------------------
1      1449           0.50000000
1      1451           1.03400000
1      2404           5.98750000
1      1454           6.00000000
3      1465           1.40000000
3      1467           1.56000000
3      1476           3.00000000
3      1469           4.00000000
3      1490           5.00000000

我希望将C列设置为递增的整数（保留小数位），并在A列更改时重新开始计数，如下所示：

colA  colB (unique)  colC
----------------------------------
1     1449           1.00000000
1     1451           2.00000000
1     2404           3.00000000
1     1454           4.00000000
3     1465           1.00000000
3     1467           2.00000000
3     1476           3.00000000
3     1469           4.00000000
3     1490           5.00000000

有什么想法可以做到这一点吗？我使用的是 SQL Server 2008。这个表可能会变得非常大。谢谢！

- John

---

这个数字需要被计算和存储吗？你可以为整个表格设置一个标识列，然后在你的查询中使用 row_number() over (partition by colA order by id) 来计算 colC 的序列号。 - Martin Smith

colA是否会回到早期的值（例如，可以在上表末尾插入colA为1的新行）？如果是这样，colC应该如何行动？我至少可以想到三种处理此问题的方法（@Martin-Smith所说的、视图和触发器），具体取决于您实际的需求。 - Andrew

Martin-- 第二个表是我们经常存储和参考的表格。colC实际上是一个排序列。第一个表格是每月从几个其他表格计算出来的，然后我们更新第二个表格。不幸的是，由于其他原因，我们需要colC成为整数。谢谢你的答案。我对视图不太熟悉，但我会对你的答案进行一些研究。同时也感谢格式化问题的人。这是我第一次使用SO。我非常印象深刻！ - John

@John: <cough> 我的答案<咳嗽> :) 视图只是一个准备好的SQL语句 - 就像宏一样。我指的是非索引/非物化视图... - OMG Ponies

Martin--非常感谢！row_number() over (partition by colA order by colC)非常好用！我猜现在需要考虑使用视图而不是存储排名值了: )。再次感谢。 - John

Answer 1

我不会存储排名值，因为它们需要维护。通常我会使用视图来处理这类问题：

CREATE VIEW your_view AS
  SELECT t.cola,
         t.colb,
         ROW_NUMBER() OVER(PARTITION BY t.cola
                               ORDER BY t.colc) AS colc
    FROM YOUR_TABLE t

但是您可以在UPDATE语句中使用WITH语句：

WITH summary AS (
  SELECT t.cola,
         t.colb,
         t.colc,
         ROW_NUMBER() OVER(PARTITION BY t.cola
                               ORDER BY t.colc) AS cold
    FROM YOUR_TABLE t)
UPDATE summary
   SET colc = cold

- OMG Ponies