比如:
查询到的结果
Name:
aa
bb
cc
dd
ee
ff想要的最终结果
No. , Name :
1 , aa
2 , bb
3 , cc
1 , dd
2 , ee
3 , ff
我查询到6条数据,我想给它加上序号,当1到3时,重新1到3这样循环
查询到的结果
Name:
aa
bb
cc
dd
ee
ff想要的最终结果
No. , Name :
1 , aa
2 , bb
3 , cc
1 , dd
2 , ee
3 , ff
我查询到6条数据,我想给它加上序号,当1到3时,重新1到3这样循环
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([Name] nvarchar(2))
Insert #
select N'aa' union all
select N'bb' union all
select N'cc' union all
select N'dd' union all
select N'ee' union all
select N'ff'
Goselect
Name,
[No]=([No]-1)%3+1
from
(Select *,[No]=row_number()over(order by (select 1)) from #)t
/*
Name No
aa 1
bb 2
cc 3
dd 1
ee 2
ff 3
*/
FROM TB
--2000select name,id=identity(int,1,1)
into #tb
from tbselect name,(id-1)%3+1 as id
from #tb
declare @t table(a varchar(100))
insert into @t
select 'aa'
union all
select 'bb'
union all
select 'cc'
union all
select 'dd'
union all
select 'ee'
union all
select 'ff'select case row_number()over(order by a)%3
when 0 then 3
when 1 then 1
when 2 then 2 end as 'No',a from @t