怎么计算纪录总数？（不好意思，暂时没有分，晚上回去绝对加上！）

[windy1]

我有这样的库：
name add totalnumber
a sh
a sz
a hn
b bj
b sh
我想让totalnumber自己计算出所有name的add的条目，
显示为下：
name add totalnumber
a sh 3
a sz 3
a hn 3
b bj 2
b sh 2
怎么写啊？
不好意思，暂时没有分了，晚上回去绝对加上！

[issacp]

--try
create table t ([name] varchar(20),[add] varchar(20))
go

insert into t values('a','sh')
insert into t values('a','sz')
insert into t values('a','hn')
insert into t values('b','bj')
insert into t values('b','sh')

select a.[name],a.[add],b.totalnumber from t a left join (select [name],count([add]) [totalnumber ] from t group by [name]) b on a.[name]=b.[name]

drop table t

[issacp]

--try
create table t ([name] varchar(20),[add] varchar(20))
go

insert into t values('a','sh')
insert into t values('a','sz')
insert into t values('a','hn')
insert into t values('b','bj')
insert into t values('b','sh')

select a.[name],a.[add],b.totalnumber from t a left join (select [name],count([name]) [totalnumber ] from t group by [name]) b on a.[name]=b.[name]

drop table t

[issacp]

--try
create table t ([name] varchar(20),[add] varchar(20))
go

insert into t values('a','sh')
insert into t values('a','sz')
insert into t values('a','hn')
insert into t values('b','bj')
insert into t values('b','sh')

select a.[name],a.[add],b.totalnumber from t a left join (select [name],count([name]) [totalnumber ] from t group by [name]) b on a.[name]=b.[name]

drop table t

[andy84]

mark

[JL99000]

主要是在实现更新上
怎么做
关注中

[-狙击手-]

update t
set totalnumber = (select count(1) from t where t.name = name)
from table t

[-狙击手-]

update t
set totalnumber = (select count(1) from table where t.name = name)
from table t

把table换成你的表名

[windy1]

ls ,谢谢，不过出来的结果是所有的数据总数！