我的表结构内容如下
a 1 1
a 2 a
a a 1
a b 3
b a 1
b c 2
b d a
我想通过一条查询语句得到如下结果
new_field
a 1 1 1
a 2 a 2
a a 1 3
a b 3 4
b a 1 1
b c 2 2
b d a 3
a 1 1
a 2 a
a a 1
a b 3
b a 1
b c 2
b d a
我想通过一条查询语句得到如下结果
new_field
a 1 1 1
a 2 a 2
a a 1 3
a b 3 4
b a 1 1
b c 2 2
b d a 3
new_field = (select count(1) from tablename where a = t1.a and b<= t1.b)
from tablename t1
insert into @t select 'a' ,'1' , '1'
union all select 'a' , '2' , 'a'
union all select 'a' , 'a' , '1'
union all select 'a' , 'b' , '3'
union all select 'b' , 'a' , '1'
union all select 'b' , 'c' , '2'
union all select 'b' , 'd' , 'a'select *,[new_field]=(select count(*) from @t where a=a.a and b<=a.b) from @t a
INSERT INTO @T SELECT 'a','1','1'
union all select 'a','2','a'
union all select 'a','a','1'
union all select 'a','b','3'
union all select 'b','a','1'
union all select 'b','c','2'
union all select 'b','d','a'select *,序号=Row_number() over (PARTITION by column1 order by column3) from @t/*
column1 column2 column3 序号
---------- ---------- ---------- --------------------
a a 1 1
a 1 1 2
a b 3 3
a 2 a 4
b a 1 1
b c 2 2
b d a 3(7 行受影响)
*/
insert into @t
select 'a', '1', '1'
union all select 'a', '2', 'a'
union all select 'a', 'a', '1'
union all select 'a', 'b', '3'
union all select 'b', 'a', '1'
union all select 'b', 'c', '2'
union all select 'b', 'd', 'a'select idt=identity(int,1,1),a.* into # from @t aselect a.col1,a.col2,a.col3,new_field=(select count(1) from # where a.col1=col1 and idt<a.idt)+1
from # a
drop table #