序号的问题

[fyming]

测试环境：
CREATE TABLE #(
ID INT IDENTITY(1,1),
F1 NVARCHAR(100),
F2 NVARCHAR(100)
)
INSERT INTO #(F1,F2)
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A2',''
----------------------------------------------
如何UPDATE #的F2，达到如下效果：
A0 1
A0 2
A0 3
A1 1
A1 2
A2 1
---------------------------
请指教!

[道素]

UPDATE a SET a.F2=b.rn FROM # AS a 
INNER JOIN(
   SELECT ID,ROW_NUMBER()OVER(PARTITION BY F1 ORDER BY ID) AS rn FROM #
) b ON a.ID=b.ID

[二月十六]

CREATE TABLE #(
ID INT IDENTITY(1,1),
F1 NVARCHAR(100),
F2 NVARCHAR(100)
)
INSERT INTO #(F1,F2)
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A2',''

UPDATE  a
SET     a.F2 = b.num
FROM    # a ,
        ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY F1 ORDER BY ID ) AS num
          FROM      #
        ) b
WHERE   a.ID = b.Id

SELECT * FROM #
DROP TABLE #

[zjl8008]

PARTITION BY F1 ORDER BY ID

[fyming]

引用 2 楼 sinat_28984567 的回复:

CREATE TABLE #(
ID INT IDENTITY(1,1),
F1 NVARCHAR(100),
F2 NVARCHAR(100)
)
INSERT INTO #(F1,F2)
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A0','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A1','' UNION ALL
SELECT 'A2',''

UPDATE  a
SET     a.F2 = b.num
FROM    # a ,
        ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY F1 ORDER BY ID ) AS num
          FROM      #
        ) b
WHERE   a.ID = b.Id

SELECT * FROM #
DROP TABLE #

真棒!!!