create table tb(
id int,
name varchar(10),
type varchar(20)
)
insert tb select 1, 'a' , '一类'
insert tb select 2 , 'b' , '二类'
insert tb select 3 , 'c', '二类'declare @c int
update a set @c=(select count(*) from tb where type=a.type and id<=a.id),
type=type+cast(@c as varchar)
from tb aselect * from tbdrop table tb/*
id name type
----------- ---------- --------------------
1 a 一类1
2 b 二类1
3 c 二类2(所影响的行数为 3 行)*/
id int,
name varchar(10),
type varchar(20)
)
insert tb select 1, 'a' , '一类'
insert tb select 2 , 'b' , '二类'
insert tb select 3 , 'c', '二类'declare @c int
update a set @c=(select count(*) from tb where type=a.type and id<=a.id),
type=type+cast(@c as varchar)
from tb aselect * from tbdrop table tb/*
id name type
----------- ---------- --------------------
1 a 一类1
2 b 二类1
3 c 二类2(所影响的行数为 3 行)*/
update a set type=type+cast((select count(*) from tb where type=a.type and id<=a.id) as varchar)
(select count(1) from tb where type=a.type and id<=a.id )as varchar(10)) from tb a