新记录重复计数的问题，在线结贴散分！

[plutu]

我想记录一个同样的字段值出现的次数，比如在增加一条姓名是“张三”的新记录时同时在记录的cishu字段中置1，然后再增加姓名是“张三”的记录时能统计出原来出现“张三”的次数，然后将原来的次数+1放到cishu字段中，我想在触发器中自动完成，可是count函数好像有点问题，请高手指点，谢谢！

[-狙击手-]

有唯一标识列吗?

[wzy_love_sly]

跟我哥们qq怎么一个名？木？

[plutu]

有唯一标识列，是另外一个字段

[plutu]

呵呵，我的QQ不是这个名

[wzy_love_sly]

create trigger tri_update_num
on tb
for insert 
as
begin
   update t1 set num=t2.num from tb t1 join (
select id,name,num=(select sum(num) from tb where name=t.name and id<=t.id) from tb t
)t2 on t1.id=t2.id
end

id是自增，如果有createdate列，也可以替换id

[-狙击手-]

cishu 在同一张有中？

[-狙击手-]

create table ta(id int identity(1,1),vname varchar(10),num int)
go

create trigger tr_name
on ta
for insert
as
   update a
   set num = (select count(1) from ta  where  id <= i.id and  vname = i.vname)
   from ta a right join inserted i on i.id = a.id
    
 
go

insert ta select 'a',null
insert ta select 'a',null
insert ta select 'b',null
insert ta select 'a',null
insert ta select 'b',null
insert ta select 'a',null

select * from ta
drop table ta

/*
id          vname      num         
----------- ---------- ----------- 
1           a          1
2           a          2
3           b          1
4           a          3
5           b          2
6           a          4

（所影响的行数为 6 行）
*/

[wzy_love_sly]

if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(20),num int)
go
create trigger tri_update_num
on tb
for insert 
as
begin
update tb set num=(
select count(1) from tb where name=t.name and id<=t.id
) from tb t
end
go


insert into tb(id,name) select 1,'a'
insert into tb (id,name)select 2,'a'
insert into tb (id,name)select 3,'a'
insert into tb(id,name) select 6,'a'
insert into tb (id,name)select 8,'a'
insert into tb (id,name)select 12,'a'

insert into tb (id,name)select 4,'b'
insert into tb (id,name)select 5,'b'
insert into tb (id,name)select 6,'b'

select * from tb

id name num
1 a 1
2 a 2
3 a 3
6 a 4
8 a 5
12 a 6
4 b 1
5 b 2
6 b 3

[plutu]

谢谢谢谢谢谢各位！再次谢谢！（要不然回复内容太短了发不出去，呵呵）