重新编号一组行

2021-09-10 00:00:00 sql sql-server-2005 tsql sql-server

我有一个包含数据的表格，如下所示:

I have a table with data which looks something like the following:

# the columns # url title group id_within_group http://www.google.com google search engine 1 http://www.yahoo.com yahoo search engine 2 http://www.bing.com bing search engine 3 http://www.facebook.com facebook social media 1 http://www.twitter.com twitter social media 2

如果我这样做

select * from table1 where group = "search engine"

我会得到

http://www.google.com google search engine 1 http://www.yahoo.com yahoo search engine 2 http://www.bing.com bing search engine 3

我希望能够删除这些行中的任何一行，但以某种方式让它重新编号 id_within_group.

I would like to be able to delete any of these rows, but somehow get it to renumber the id_within_group.

所以，如果我从上面的 3 个中删除第二个，它应该自动重新编号然后留给我

SO if I delete the second one from the 3 above, it should automatically renumber then leaving me with

http://www.google.com google search engine 1 http://www.bing.com bing search engine 2

这可能吗?

推荐答案

如果您只需要显示连续整数，我的建议是在 SELECT 中执行此操作.

My suggestion would be to do this in the SELECT if you just need to display a sequential integer.

SELECT url , title , [group] , id_within_group , ROW_NUMBER() over (partition BY [group] ORDER BY id_within_group) AS sequential_index FROM YourTable

相关文章