27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #TP
(
AutoId INT,
ModuleId INT,
SEQ INT,
DisplayName VARCHAR(10)
)
INSERT INTO #TP
SELECT 134,7001,1,'Work'
UNION ALL
SELECT 135,7001,3,'Work'
UNION ALL
SELECT 136,7001,6,'Work'
UNION ALL
SELECT 137,7002,4,'Work'
UNION ALL
SELECT 138,7002,9,'Work'
UNION ALL
SELECT 139,7003,8,'Work'
SELECT * FROM #TP t
UPDATE t
SET SEQ=T.ROW
FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY MODULEID ORDER BY AUTOID) ROW
FROM #TP) t
SELECT * FROM #TP t
AutoId ModuleId SEQ DisplayName
----------- ----------- ----------- -----------
134 7001 1 Work
135 7001 2 Work
136 7001 3 Work
137 7002 1 Work
138 7002 2 Work
139 7003 1 Work
(6 row(s) affected)
--不管数据库怎么存,读取的时候动态生成就行了。
--修改或删除都会在重新帮顶一次吧,这样就会联系了。
select Seq=row_number() over(partition by ModuleId order by AutoId) from tb
AutoId ModuleId Seq DisplayName
134 7001 1 Work Order No
135 7001 2 Style No
136 7001 3 Order Qty
137 7001 4 Cut Qty
138 7001 5 Previous Qty1
139 7001 6 Current Qty1
140 7001 7 Total Qty1
141 7001 8 Balance Qty1
214 7002 1 Worker No
215 7002 2 Name
216 7002 3 Other Name
217 7002 4 Department Code
218 7002 5 Department Name