首页 新闻 搜索 专区 学院

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

0
悬赏园豆:100 [待解决问题]

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有没有自带的函数可以实现这种排序

你奈我何的主页 你奈我何 | 初学一级 | 园豆:102
提问于:2021-12-02 14:30
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册