select id = identity(int,1,1) , num into tmp from tbselect px = 150*(id / 9 + 1) + 10 * (id / 9) + ((id - 1) % 9 + 1) , num from tmp
if OBJECT_ID('tempdb.dbo.#temp') is not null drop table #temp go create table #temp(rowno int, num int) insert #temp(num) select 33 union all select 12 union all select 11 union all select 32 union all select 11 union all select 10 union all select 31 union all select 10 union all select 9 union all select 30 union all select 9 union all select 8 go --SQL: declare @counter_1 int, @counter_2 int select @counter_1 = 14, @counter_2 = 0 update #temp set rowno = @counter_1 * 1000 + @counter_2, @counter_2 = case when @counter_2 < 9 then (@counter_2 + 1) else 1 end, @counter_1 = case when @counter_2 = 1 then (@counter_1+1) else @counter_1 endselect * from #temp /* rowno num 15001 33 15002 12 15003 11 15004 32 15005 11 15006 10 15007 31 15008 10 15009 9 16001 30 16002 9 16003 8 */
-- 这里的10是 100/10=10分10个组
select n=NTILE(10) over(order by id ),* from t;
select n=(select COUNT(1) from t b where b.id<a.id)/10+1,* from t a
num
33
12
..
..
2 共有90记录,要求每9个编号1501 33
1502 12
.. ..
1509 5
1601 9
1602 11
.. ..
.. ..
2409 22000系统
go
create table #temp(rowno int, num int)
insert #temp(num)
select 33 union all select 12 union all select 11 union all
select 32 union all select 11 union all select 10 union all
select 31 union all select 10 union all select 9 union all
select 30 union all select 9 union all select 8
go
--SQL:
declare
@counter_1 int,
@counter_2 int
select
@counter_1 = 14,
@counter_2 = 0
update #temp
set rowno = @counter_1 * 1000 + @counter_2,
@counter_2 = case when @counter_2 < 9 then (@counter_2 + 1) else 1 end,
@counter_1 = case when @counter_2 = 1 then (@counter_1+1) else @counter_1 endselect * from #temp
/*
rowno num
15001 33
15002 12
15003 11
15004 32
15005 11
15006 10
15007 31
15008 10
15009 9
16001 30
16002 9
16003 8
*/