sql 2000 实现partition by rownumber功能

最新推荐文章于 2024-10-25 15:27:57 发布

原创最新推荐文章于 2024-10-25 15:27:57 发布 · 944 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #table #insert #null #c

sql 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了如何使用SQL语句创建表、插入数据，并通过查询计算特定条件下的统计数据。

declare @a table(id nvarchar(10),num int)
insert into @a
select '000019',1 union all
select '000019',0 union all
select '000019',0 union all
select '000019',0 union all
select '000020',0 union all
select '000020',0 union all
select '000020',0 union all
select '000021',0 union all
select '000021',0 union all
select '000021',0

select identity(int,1,1) iden,id into #a from @a
select
num= isnull((select case when iden is null then 0 else
(select COUNT(*) from #a c where c.id = B.id and c.iden <= B.iden)
end from #a b where a.iden = b.iden + 1 and B.id = a.id ),0)+1
,id from #a a

drop table #a