select a,b
,count1=(select count(*) from table1 where b=a.b)
,count1=(select count(*) from table1 where b=a.b and a<=a.a)
from table1 a
,count1=(select count(*) from table1 where b=a.b)
,count1=(select count(*) from table1 where b=a.b and a<=a.a)
from table1 a
select a,b
,count1=(select count(*) from table1 where b=a.b)
,count1=(select count(*) from table1 where b=a.b and a<a.a)
from table1 a
create table table1(a int,b int)
insert table1
select 1,1
union all select 2,1
union all select 3,2
union all select 4,2
union all select 5,1
union all select 6,2
go--查询
select a,b
,count1=(select count(*) from table1 where b=a.b)
,count1=(select count(*) from table1 where b=a.b and a<a.a)
from table1 a
go--删除测试
drop table table1/*--测试结果
a b count1 count1
----------- ----------- ----------- -----------
1 1 3 0
2 1 3 1
3 2 3 0
4 2 3 1
5 1 3 2
6 2 3 2(所影响的行数为 6 行)
--*/