# sql 如何实现每次排序都从1开始排序，有哪位大神知道怎么排

0

create table #t(
id int identity(1,1),
eno int,
eid int,
tum int,
sstatus int,
sums int
)

insert into #t(eno,eid,tum,sstatus,sums)
values('1','9','1','1','10'),
('1','9','1','1','10'),
('1','9','1','0','10'),
('1','9','1','0','10'),
('1','9','1','0','10'),
('1','9','1','0','10'),
('1','9','1','1','10'),
('1','9','1','1','10'),
('1','9','1','1','10'),
('1','9','1','1','10'),
('1','9','1','0','10'),
('1','9','1','0','10'),
('1','9','1','0','10')

select * from #t

select *,row_number() over(partition by sstatus ,flag order by id) from (
select *,sum(case when (select b.sstatus from #t b where b.id=a.id-1)=a.sstatus then null else 1 end) over (order by id) as flag
from #t a)a

declare @t TABLE(ID INT IDENTITY, sstatus varchar(max) NOT NULL,eno datetime,eid int,tum varchar(100),sums varchar(100),T INT DEFAULT(1))

INSERT into @t (eno,eid,tum,sstatus,sums)
select eno,eid,tum,sstatus,sums
from #t;

WITH sstatus AS (
SELECT * FROM @t WHERE ID=1
UNION ALL
SELECT t.ID,t.sstatus,t.eno,t.eid,t.tum,t.sums,CASE WHEN c.sstatus=t.sstatus THEN c.T+1 ELSE 1 END
FROM @t t INNER JOIN sstatus c ON c.ID=t.ID-1
)
SELECT * into #sstatus1 FROM sstatus
OPTION(MAXRECURSION 0)

select * from #sstatus1

sql server有没有自带的函数可以实现这种排序

您需要登录以后才能回答，未注册用户请先注册