select a.*,(select count(*) + 1 as 流水号 from tablename where 编码 < a.编码 and 分类 = a.分类), b.总数
from tablename join (select 分类, count(编码) as 总数 from tablename group by 分类) b on a.分类 = b.分类
from tablename join (select 分类, count(编码) as 总数 from tablename group by 分类) b on a.分类 = b.分类
select
编码,
分类,
水号=(select sum(1) from 表 where 分类=A.分类 and 编码<=A.编码),
总数=(select coun(分类) from 表 where 分类=A.分类)
from 表 A
select 'A001','AAA ' union all
select 'A002','AAA' union all
select 'A003','AAA' union all
select 'B001','BBB' union all
select 'B002','BBB' union all
select 'C001','CCC' update @tb
set 流水号=(select count(*) from @tb where 编码<=a.编码 and 分类=a.分类),
总数=(select count(*) from @tb where 分类=a.分类)
from @tb aselect * from @Tb
(select count(*) + 1 as 流水号
from tablename
where 编码 < a.编码 and 分类 = a.分类),
(select count(*) as 总数
from tablename
where 分类 = a.分类 )
from tablename a
insert into @t select 'A001','AAA'
union all select 'A002','AAA'
union all select 'A003','AAA'
union all select 'B001','BBB'
union all select 'B002','BBB'
union all select 'C001','CCC'
--查询
select
编码,
分类,
水号=(select sum(1) from @t where 分类=A.分类 and 编码<=A.编码),
总数=(select count(分类) from @t where 分类=A.分类)
from @t A
--结果
/*
编码 分类 水号 总数
---------- ---------- ----------- -----------
A001 AAA 1 3
A002 AAA 2 3
A003 AAA 3 3
B001 BBB 1 2
B002 BBB 2 2
C001 CCC 1 1(所影响的行数为 6 行)
*/
select 'A001','AAA ' union all
select 'A002','AAA' union all
select 'A003','AAA' union all
select 'B001','BBB' union all
select 'B002','BBB' union all
select 'C001','CCC' select a.*,
(select count(*) + 1
from @tb
where 编码 < a.编码 and 分类 = a.分类) as 流水号 ,
(select count(*)
from @tb
where 分类 = a.分类 ) as 总数
from @tb a/*编码 分类 流水号 总数
-------------------- ---------- ----------- -----------
A001 AAA 1 3
A002 AAA 2 3
A003 AAA 3 3
B001 BBB 1 2
B002 BBB 2 2
C001 CCC 1 1
*/