--建立测试环境
Create Table 表(c1 varchar(10),c2 varchar(10),c3 varchar(10))
--插入数据
insert into 表
select '01','a','1' union
select '01','a','2' union
select '01','a','3' union
select '01','b','4' union
select '01','b','5' union
select '01','c','6' union
select '01','c','7' union
select '02','a','8' union
select '02','a','9' union
select '02','b','10' union
select '02','b','11' union
select '02','c','12'
--select * from 表
--测试语句
select id=identity(int),* into #t from 表
select c1,c2,c3=(select count(*) from #t where c2=a.c2 and c1=a.c1 and id<=a.id)
from #t a
drop table #t
--删除测试环境
Drop Table 表/*
c1 c2 c3
---------- ---------- -----------
01 a 1
01 a 2
01 a 3
01 b 1
01 b 2
01 c 1
01 c 2
02 a 1
02 a 2
02 b 1
02 b 2
02 c 1
*/
Create Table 表(c1 varchar(10),c2 varchar(10),c3 varchar(10))
--插入数据
insert into 表
select '01','a','1' union
select '01','a','2' union
select '01','a','3' union
select '01','b','4' union
select '01','b','5' union
select '01','c','6' union
select '01','c','7' union
select '02','a','8' union
select '02','a','9' union
select '02','b','10' union
select '02','b','11' union
select '02','c','12'
--select * from 表
--测试语句
select id=identity(int),* into #t from 表
select c1,c2,c3=(select count(*) from #t where c2=a.c2 and c1=a.c1 and id<=a.id)
from #t a
drop table #t
--删除测试环境
Drop Table 表/*
c1 c2 c3
---------- ---------- -----------
01 a 1
01 a 2
01 a 3
01 b 1
01 b 2
01 c 1
01 c 2
02 a 1
02 a 2
02 b 1
02 b 2
02 c 1
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货