select *,tid=identity(int,1,1) into # from tbupdate a set a.id=a.id+b.tid/6 from tb a,# b where a.id=b.id
declare @a table(id int ) insert @a select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 3 union all select 3 union all select 1 union all select 1 union all select 1 union all select 1 union all select 4 -------------------------------- declare @n int ,@m int set @n=1 set @m=1update @a set @n=(case when @m+1%6<>0 then @n else @n+1 end),@m=@m+1,id=id+@n -------------------------------- select * from @a id ----------- 2 2 2 2 2 2 3 3 3 3 3 3 4 4 2 2 2 2 5(19 行受影响)
select distinct top 1000 number into #T from master..spt_values where number>0 drop table #T select (number+5)/6 as row from #T
select id+1 as is from test_table此列每6行+1 = 此列所有数据+1 ? 此前数据已经排好6个并且6个都相同吧?
ALTER TABLE [A] ADD [OrderField] Int Identity(1, 1); GO UPDATE [A] SET [ID] = [OrderField] / CAST(6 AS Int); GO ALTER TABLE [A] DROP COLUMN [OrderField]; GO
set a.id=a.id+b.tid/6
from tb a,# b
where a.id=b.id
declare @a table(id int )
insert @a select
1 union all select
1 union all select
1 union all select
1 union all select
1 union all select
1 union all select
2 union all select
2 union all select
2 union all select
2 union all select
2 union all select
2 union all select
3 union all select
3 union all select
1 union all select
1 union all select
1 union all select
1 union all select
4
--------------------------------
declare @n int ,@m int
set @n=1
set @m=1update @a
set @n=(case when @m+1%6<>0 then @n else @n+1 end),@m=@m+1,id=id+@n
--------------------------------
select * from @a
id
-----------
2
2
2
2
2
2
3
3
3
3
3
3
4
4
2
2
2
2
5(19 行受影响)
select distinct top 1000 number into #T from master..spt_values where number>0
drop table #T
select (number+5)/6 as row from #T
ALTER TABLE [A] ADD [OrderField] Int Identity(1, 1);
GO
UPDATE [A] SET [ID] = [OrderField] / CAST(6 AS Int);
GO
ALTER TABLE [A] DROP COLUMN [OrderField];
GO