使用编程增加 SQL Server 中的值

发布于 2024-10-16 06:05:47 字数 495 浏览 2 评论 0原文

我在 SQL Server 中有一个表，它有 3 个字段，

Id int identity(1,1)
thRead int
level int

现在这个表中有线程，如 1-5，所有线程都重复 5 次，如下所示：

 1,1,1,1,1 2,2,2,2,2 3,3,3,3,3 4,4,4,4,4 5,5,5,5,5

现在我想更新级别，以便对于一组记录，它应该递增从零开始，另一组又从 0 开始，依此类推..

我想要如下表所示的输出....

请任何人都可以帮我解决这个问题...更新应该使用选择查询，因此无需输入线程手动，它应该自动更新

谢谢和问候阿巴斯电气瓦拉

原文

I have a table in SQL Server which has 3 fields,

Id int identity(1,1)
thRead int
level int

Now in this table there are threads, like 1-5, all are repeating 5times, like this:

 1,1,1,1,1 2,2,2,2,2 3,3,3,3,3 4,4,4,4,4 5,5,5,5,5

Now I want to update level such that, for a group of records it should increment starting from zero, for another group again from 0 and so on..

I want the output like table below....

Please can anyone help me out with this... the update should be with select query so no need to enter thread manually, it should update automatically

Thanks and Regards
Abbas Electricwala

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

摇划花蜜的午后 2024-10-23 06:05:47

这是一次性更新吗？如果是这样的话，这会起作用。

with cte as
(
SELECT Id , thRead, level,
       ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 AS RN
FROM T
)
UPDATE cte
SET level = RN

如果您想对新行持续执行此操作，那就更困难了。

Is this a one off update? If so this would work.

with cte as
(
SELECT Id , thRead, level,
       ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 AS RN
FROM T
)
UPDATE cte
SET level = RN

If you want to do this ongoing for new rows that is more difficult.

回复收藏 0 原文

忆沫 2024-10-23 06:05:47

你可以试试这个

;WITH Temp as
(
 SELECT Id , thRead, ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 'Level' 
 FROM YourTable
)
Select Id, ThRead, Level from Temp

You can try this

;WITH Temp as
(
 SELECT Id , thRead, ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 'Level' 
 FROM YourTable
)
Select Id, ThRead, Level from Temp

回复收藏 0 原文

~没有更多了~