sql-server-2000 - SQL 2000 使用 GROUP BY 进行行编号

Question

这是我的 table

################################
# id_formfield # ID # int_Sort #
################################
#      1       # 1  #     2    #
#      2       # 1  #     3    #
#      3       # 1  #     4    #
#      4       # 1  #     4    #
#      5       # 1  #     4    #
#      6       # 2  #     1    #
#      7       # 2  #     3    #
#      8       # 2  #     3    #
#      9       # 2  #     4    #

正如您所看到的，我的 int_sort 列以某种方式被相同的数字弄乱了，我想使其与行号排序并在 ID 更改时重置行号。
这应该是结果:

############################################
# id_formfield # ID # int_Sort # rownumber #
############################################
#      1       # 1  #     2    #     1     #
#      2       # 1  #     3    #     2     #
#      3       # 1  #     4    #     3     #
#      4       # 1  #     4    #     4     #
#      5       # 1  #     4    #     5     #
#      6       # 2  #     1    #     1     #
#      7       # 2  #     3    #     2     #
#      8       # 2  #     3    #     3     #
#      9       # 2  #     4    #     4     #

编辑:id_formfield 是我的主键和按 int_sort 升序排序

Answer 1

以下脚本应该可以帮助您入门。简而言之，脚本

创建一个临时表，添加一个 IDENTITY列作为行号

将原始数据插入临时表

用途 MIN(Rownumber)每个ID得到一个抵消。

JOIN具有计算偏移量的临时表以重新开始每个组的计数。

SQL 语句

CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)
SET IDENTITY_INSERT #TempTable OFF

INSERT INTO #TempTable
SELECT      *
FROM        YourOriginalTable
ORDER BY    ID, int_Sort

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            


DROP TABLE #TempTable

测试脚本

DECLARE @YourTable TABLE (ID VARCHAR(1))
CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)

SET IDENTITY_INSERT #TempTable OFF
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)

INSERT INTO #TempTable
SELECT      *
FROM        @YourTable
ORDER BY    ID

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            


DROP TABLE #TempTable